Group by column and string join

[syezids (syd)]

hi, how can i group by Project, and combine the Order ID for the rows grouped.

expected output:

thanks

[Anil_G (Anil Gorthi)]

@syezids

Please try this in assign

dtOutput = 
(From d In DtInput.AsEnumerable() 
Group d By k=d("Project").toString.Trim Into grp = Group 
Let jn = String.Join(",",grp.Select(function(x) x("Order ID").ToString)) 
Let ra = New Object(){k,jn} 
Select r = DTOutput.Rows.Add(ra)).CopyToDataTable()

First create a datatatble with two columns using Build Datatble activity ,dtInput is the Input DataTable and dtoutput is the output table which is created using build datatable activity

cheers

[arjunshenoy]

Hi @syezids

Use the following query for this:

(From row In dt.AsEnumerable()
Group row By orderId=row("Order ID") Into Group
Let concatenatedValues = String.Join(",", Group.Select(Function(r) r("Column1").ToString() + "," + r("Column2").ToString() + "," + r("Column3").ToString()))
Select dtResult.Rows.Add(orderId, concatenatedValues)).CopyToDataTable()

Sample Input:

Order ID	Product	Quantity
1001	Apple	2
1001	Orange	3
1002	Banana	4
1003	Mango	1
1003	Pineapple	2
1003	Watermelon	2

Sample Output:

Order ID	Products
1001	Apple,Orange
1002	Banana
1003	Mango,Pineapple,Watermelon

You can change the query according to your need.

Hope this helps,
Best Regards.

[syezids (syd)]

hi @arjunshenoy and @Anil_G ,
both of your query gives me duplicate rows:

i want the remaining rows to be omit

[syezids (syd)]

@Anil_G @arjunshenoy nvm its my fault, im assigning the wrong dt for output

thanks for fast response!