DataTable group by questions

I need to group by a column in a data table and combine all the values in a column for each group, values separated by semicolons. What is the efficient way of doing this? Filter table does not have a group by. Thank you,


Can you share specific input data and expected output as file? It’s no problem if dummy data.


Hey @cury you can do grouping and joining using linq query in a more efficient way. Can you please provide a sample input and output file…


Sreejith S S

City and County are two of the columns in the data table.

Country City …
USA Newyork
Canada Toranto
Canada Montreal
Canada Victoria

I need to group by country and for each of the country join cities with a separator ;

Thank you,


Can you try the following expression?

dt = dt.AsEnumerable.GroupBy(Function(r) r("Country").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,String.Join(";",g.Select(Function(r) r("City").ToString))},False)).CopyToDataTable (2.8 KB)


It worked perfect. Thanks!

Thanks aswell.

1 Like

Hi @cury

How about this expression?

Use Assign activity DtOutput = DtBuild.clone

(From d In DtBuild.AsEnumerable
Group d By k=d("Country ").toString.Trim Into grp = Group
Let nj = String.Join(";",grp.Select(Function (n) n("City").toString.Trim))
Let ra = New Object(){k,nj}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable


Thank you @Gokul001

Nice query.

Worked great. Thanks,

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.