Group by

Help Studio
1

Hi all,

i’ve used the following linq:

(From d In ioDtReport.AsEnumerable
Group d By k9=d(“A”) Into grp=Group
Let n = String.Join("; ",grp.Select(Function (x) x(“C”).toString.Trim).toArray)
Let b = grp.Where(Function (x) Not(String.IsNullOrEmpty(x(“B”).ToString)))
Let ra = New Object(){k9, b ,n}
Select ioDtReport.Rows.Add(ra)).CopyToDataTable

I’ve the following DT in input:

A B C
1 “” 2
1 OK 3
2 “” 3

I want to group by for column “A”, join all the results contained in the “C” column for the group, and also i want to take, for column “B”, if exist, just the value not empty, so, in my case the result:

A B C
1 OK 2;3
2 “” 3

How can i adjust the Linq?
Thanks in advance

2

dtResult = dtData.Clone

dtResult =
(From d In dtData.AsEnumerable
Group d By k=d("A").toString.Trim Into grp=Group
Let c = String.Join("; ", grp.Select(Function (x) x("C").toString.Trim) )
Let b = grp.Where(Function (x) Not ( IsNothing(x("B")) OrElse String.IsNullOrEmpty(x("B").ToString.Trim) ) ).Select(Function (x) x("B")).FirstOrDefault()
Let ra = New Object(){k, b ,c}
Select dtResult.Rows.Add(ra)).CopyToDataTable

adapt the datatable var names as defined at your end.

3

Hi @andreus91

I believe there is a small correction to what is provided above. In the above join gives result joined twice.

(From d In ioDtReport.AsEnumerable
Group d By k=d("A").toString.Trim Into grp=Group
Let c = String.Join("; ", String.Join("||",grp.Where(Function(x) Not x("C").ToString.Contains("||")).
Select(Function(x) x("C").ToString)).Split("||",StringSplitOptions.None).Take(CInt(String.Join("||",grp.Where(Function(x) Not x("C").ToString.Contains("||")).
Select(Function(x) x("C").ToString)).Split("||",StringSplitOptions.None).Count-1)))
Let b = grp.Where(Function (x) Not ( IsNothing(x("B")) OrElse String.IsNullOrEmpty(x("B").ToString.Trim) ) ).Select(Function (x) x("B")).FirstOrDefault()
Let ra = New Object(){k, b ,c}
Select ioDtReport.Rows.Add(ra)).CopyToDataTable

cheers

4

HI @andreus91

Check out this XAML file

LinqGroupby.xaml (7.8 KB)

image

Regards
Gokul