DataTable group by questions

[cury]

Hi,
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,

[Yoichi]

HI,

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

Regards,

[sreejith.ss]

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…

Regards

Sreejith S S

[cury]

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

Country City …
USA DC
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,

[Yoichi]

Hi,

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

Sample20221014-7.zip (2.8 KB)

Regards,

[cury]

@Yoichi
It worked perfect. Thanks!

Thanks @sreejith.ss aswell.

[Gokul001 (Gokul Balaji)]

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

Regards
Gokul

[cury]

Thank you @Gokul001

Nice query.

[cury]

@Gokul001
Worked great. Thanks,