LINQ expression for Data Table agregation

[SONYC]

Hello,

I have the next data table

Database	Role	Reference
Database A	ONE	OPS,DEV
Database B	ONE	OPS,DEV
Database C	ONE	DEV
Database A	ADMIN	OPS,DEV
Database A	TWO	BA
Database A	THREE	BA
Database C	ADMIN	BA
Database B	FOUR	DEV
Database A	ALL	MONITOR

I want to group all the rows by “Database” and have all the “Roles” & “References” of the same datebase agregated in the same cell. The last two columns “Role” & “Reference” can be of Type String OR Type Array.

In the result, I want to obtain this sorted data table

Could you please help me with a LINQ Query to group the databases and select many roles and references ?

Thank you in advance.

[ppr (Peter Preuss)]

@SONYC
Welcome to the forum

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

Assign Activity
dtResult = yourOrigDTVar.Clone

Assign Activity
dtResult =

(From d in yourOrigDTVar.AsEnumerable
Group d by k=d("Database").toString.Trim into grp=Group
Let jr = String.Join(",",grp.Select(Function (x) x("Role")))
Let rr = String.Join(",",grp.Select(Function (x) x("Reference")))
Let ra = New Object(){k, jr, rr}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

[SONYC]

Thank you Peter!

I get the expected result except the thing that I want to remove the duplicate values.

I tryed this, but it’s taking only the value from the first line of the database group.

(From d in yourOrigDTVar.AsEnumerable
Group d by k=d(“Database”).toString.Trim into grp=Group
Let jr = String.Join(“,”,grp.Select(Function (x) x(“Role”)).First)
Let rr = String.Join(“,”,grp.Select(Function (x) x(“Reference”)).First)
Let ra = New Object(){k, jr, rr}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

[Yoichi]

Hi,

@SONYC

I want to remove the duplicate values.

We need to use Distinct method as the following, for example.

(From d In dt.AsEnumerable
Group d By k=d("Database").toString.Trim Into grp=Group
Let jr = String.Join(",",grp.Select(Function (x) x("Role")).Distinct)
Let rr = String.Join(",",grp.Select(Function (x) x("Reference")).Distinct)
Let ra = New Object(){k, jr, rr}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Sample20221217-1aL.zip (2.9 KB)

Regards,

[SONYC]

Thank you Yoichi!

I just managed to use .Distinct method and I was about to post the solution.

Thank you both for your help!

[SONYC]

I have one more question about this topic.

How could I ignor case while selecting distinct values ?

I might have the same reference in lower case and in upper case as well.

[Yoichi]

Hi,

How could I ignor case while selecting distinct values ?

How about the following?

(From d In dt.AsEnumerable
Group d By k=d("Database").toString.Trim Into grp=Group
Let jr = String.Join(",",String.Join(",",grp.Select(Function (x) x("Role").ToString)).Split(","c).Distinct(StringComparer.InvariantCultureIgnoreCase))
Let rr = String.Join(",",String.Join(",",grp.Select(Function (x) x("Reference").ToString)).Split(","c).Distinct(StringComparer.InvariantCultureIgnoreCase))
Let ra = New Object(){k, jr, rr}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Regards,

[SONYC]

Thank you again Yoichi!

The split was necessary as I still got the result with duplicate values in some cases where I had multiple references separated by “,” in the same cell.

This expression fits perfectly to my needs.

Kind regards.