Datatable group multiple columns by list

Help Studio
, ,
1

I have a datatable as below:

Product ID Product Name Component Component ID Component Available
1 Prod1 Comp1 1 Yes
1 Prod1 Comp2 2 No
1 Prod1 Comp3 3 No
2 Prod2 Comp1 1 Yes
2 Prod2 Comp2 2 No
2 Prod2 Comp3 3 Yes
2 Prod2 Comp4 4 No

I want to group it by list and get a table as below:

Product ID Product Name Component Component ID Component Available
1 Prod1 Comp1,Comp2,Comp3 1,2,3 Yes,No, No
2 Prod2 Comp1,Comp2,Comp3, Comp4 1,2,3,4 Yes,No,Yes,No

Please, how do I achieve this?

2

have a look here:

and also here:

3

How do I combine multiple columns to group by? This is where I am getting wrong.
Group d By k={d("Product ID").toString.Trim, d("Product NAme").toString.Trim} Into grp=Group

Can you kindly clarify?

4

prepare a target datatable by
dtTarget = yourOriginDataTableVar.Clone

then try following LINQ


(From d in yourOriginDataTableVar.AsEnumerable
Group d by k1=d("Product ID").toString.Trim, k2=d("Product Name").toString.Trim Into grp=Group
let cm = String.Join(",", grp.Select(Function (x) x("Component"))
let cmi = String.Join(",", grp.Select(Function (x) x("Component ID"))
let cma =  String.Join(",", grp.Select(Function (x) x("Component Available"))
let ra = new Object(){k1,k2,cm,cmi,cma}
Select dtTarget.Rows.Add(ra)).CopyToDataTable
1 Like
5

It works perfectly!
Thanks

6

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