LINQ: group data and concat

[Vandekamp]

Hello,
I have this dt as input:
Name | Quantity | Ref
A | 4 | 104
A | 8 | 105
B | 3 | 300
C | 6 | 400

I would like this output:
Name | Quantity | Ref
A | 12 | 104 (4), 105 (8)
B | 3 | 300
C | 6 | 400

With this code

(From d In in_myDt.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let qty = grp.Sum(Function (s) CInt(s("Quantity").toString.Trim))
Let ra = New Object(){grp.First()("Name"), qty, grp.First()("Ref") }
Select r = dt_Reporting.Rows.Add(ra)).CopyToDataTable

I get this dt
Name | Quantity | Ref
A | 12 | 104
B | 3 | 300
C | 6 | 400

The problem is that I only get the first REf (104) but I need all the Ref with the same Name and their respective Qty.
I don’t know how to concat all the ref with their respective Qty. In this example, I have 2 rows for A, but I could have 5 rows or more.

Thank you for your help

[pikorpa (Piotr Kołakowski)]

Hey @Vandekamp
try this:

(From d In in_myDt.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let qty = grp.Sum(Function (s) CInt(s("Quantity").toString.Trim))
Let refs = String.Join(", ", grp.Select(Function(x) x("Ref").ToString.Trim & " (" & x("Quantity").ToString.Trim & ")"))
Let ra = New Object(){k, qty, refs}
Select r = dt_Reporting.Rows.Add(ra)).CopyToDataTable

[Vandekamp]

Thank you so much Pikorpa!!!

[srinivasmarneni (Srinivas M)]

Step 1: Initialize Structures

• *Input DataTable (in_myDt)**: The DataTable you are starting with.
• *Output DataTable (outputDt)**: A new DataTable with the same schema as your input but prepared to store the aggregated results.

Step 2: Create and Populate the Output DataTable

’ Initialize the output DataTable with desired structure
Dim outputDt As New DataTable
outputDt.Columns.Add(“Name”, GetType(String))
outputDt.Columns.Add(“Quantity”, GetType(Int32))
outputDt.Columns.Add(“Ref”, GetType(String))

’ Use a Dictionary to keep track of names and their corresponding index in outputDt
Dim nameIndexDict As New Dictionary(Of String, Integer)

For Each row As DataRow In in_myDt.Rows
Dim name As String = row(“Name”).ToString()
Dim quantity As Integer = Int32.Parse(row(“Quantity”).ToString())
Dim ref As String = row(“Ref”).ToString()

If nameIndexDict.ContainsKey(name) Then
    ' Update existing row in outputDt
    Dim index As Integer = nameIndexDict(name)
    outputDt.Rows(index)("Quantity") += quantity
    outputDt.Rows(index)("Ref") = outputDt.Rows(index)("Ref").ToString() & ", " & ref & " (" & quantity.ToString() & ")"
Else
    ' Add new row in outputDt and store its index in nameIndexDict
    outputDt.Rows.Add(name, quantity, ref & " (" & quantity.ToString() & ")")
    nameIndexDict.Add(name, outputDt.Rows.Count - 1)
End If

Next

Note: This approach provides a clear, step-by-step method for aggregating your data, suitable for those who prefer a more procedural style over LINQ

[ppr (Peter Preuss)]

(From d In in_myDt.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let qty = grp.Sum(Function (s) CInt(s("Quantity").toString.Trim))
Let cva = grp.Select(Function (g) String.Format("{0} ({1})", g("Ref").toString, g("Quantitiy").toString)) 
Let cvj = String.Join(", ", cva)
Let ra = New Object(){k, qty, cvj }
Select r = dt_Reporting.Rows.Add(ra)).CopyToDataTable

there is small difference as B,3,300 will be present as B,3, 300 (3)

but we could think about to handle grp.Count = 1 and grp.Count > 1 different when it is needed