VB.NET - Combine rows in DataTable based on shared value

Question

I'm trying to combine rows in a DataTable based on their shared ID. The table data looks something like this:

Member | ID   | Assistant | Content
---------------------------------------------
16     | 1234 | jkaufman  | 1/1/2015 - stuff1
16     | 1234 | jkaufman  | 1/2/2015 - stuff2
16     | 4321 | mhatfield | 1/3/2015 - stuff3
16     | 4321 | mhatfield | 1/4/2015 - stuff4
16     | 4321 | mhatfield | 1/5/2015 - stuff5
16     | 5678 | psmith    | 1/6/2015 - stuff6

I want to combine rows based on matching IDs. There are two steps I could use some clarification on. The first is merging the rows. The second is combining the Content columns so that the contents aren't lost. For the example above, here's what I want:

Member | ID   | Assistant | Content
-------------------------------------------------------------------------------------------
16     | 1234 | jkaufman  | 1/1/2015 - stuff1 \r\n 1/2/2015 - stuff2
16     | 4321 | mhatfield | 1/3/2015 - stuff3 \r\n 1/4/2015 - stuff4 \r\n 1/5/2015 - stuff5
16     | 5678 | psmith    | 1/6/2015 - stuff6

My eventual goal is copy the DataTable to an Excel spreadsheet so I'm not sure sure if the \r\n is the correct newline character but that's the least of my concerns at this point.

Here's my code right now (EDIT: updated to current code):

Dim tmpRow As DataRow
dtFinal = dt.Clone()

Dim i As Integer = 0
While i < dt.Rows.Count
    tmpRow = dtFinal.NewRow()
    tmpRow.ItemArray = dt.Rows(i).ItemArray.Clone()
    Dim j As Integer = i + 1
    While j <= dt.Rows.Count
        If j = dt.Rows.Count Then 'if we've iterated off the end of the datset
            i = j
            Exit While
        End If
        If dt.Rows(i).Item("ID") = dt.Rows(j).Item("ID") Then 'if we've found another entry for this id
            'append change to tmpRow
            tmpRow.Item("Content") = tmpRow.Item("Content").ToString & Environment.NewLine & dt.Rows(j).Item("Content").ToString
        Else 'if we've run out of entries to combine
            i = j
            Exit While
        End If

        j += 1
    End While
    'add our combined row to the final result
    dtFinal.ImportRow(tmpRow)
End While

When I export the final table to Excel, the spreadsheet is blank so I'm definitely doing something wrong.

Any help would be fantastic. Thanks!

Answer 1

I see various problems with your approach (with both versions; but the second one seems better). That's why I have preferred to write a whole working code to help transmit my ideas clearly.

    Dim dtFinal As DataTable = New DataTable
    For Each col As DataColumn In dt.Columns
        dtFinal.Columns.Add(col.ColumnName, col.DataType)
    Next

    Dim oldRow As Integer = -1
    Dim row As Integer = -1
    While oldRow < dt.Rows.Count - 1

        dtFinal.Rows.Add()
        row = row + 1

        oldRow = oldRow + 1
        Dim curID As String = dt.Rows(oldRow)(1).ToString()
        Dim lastCol As String = ""
        While (oldRow < dt.Rows.Count AndAlso dt.Rows(oldRow)(1).ToString() = curID)
            lastCol = lastCol & dt.Rows(oldRow)(3).ToString() & Environment.NewLine
            oldRow = oldRow + 1
        End While

        oldRow = oldRow - 1

        For i As Integer = 0 To 2
            dtFinal.Rows(row)(i) = dt.Rows(oldRow)(i)
        Next
        dtFinal.Rows(row)(3) = lastCol

    End While

Note that trying to come up with the most "elegant" solution or to maximise the given in-built functionalities might not be the best way to face certain situations. In the problem you propose, for example, I think that it is better going step by step (and reducing code size/improving elegance only after a properly working version is in place). This is the kind of code I have tried to create here: a simple one delivering what is expected (I think that this is the exact functionality you want; in any case, bear in mind that I am including a simplistic code which you are expected to take as a mere help to understand the point).