Combining Datarows based on Unique Columns

[MichaelL]

Hello all,

I need help to transform Datatable 1 to Datatable 2. It would be good if we have linq solutions to this and cater for any number of columns. Any help is appreciated. Thanks!

Datatable 1:

Datatable 2:

[ppr (Peter Preuss)]

@MichaelL

give a try on following prototype

assumptions: Group By by Country, Property, Month
to combine all other cols after Month

(From d In dtData.AsEnumerable
Group d By k1=d(0).toString,k2=d(1).toString,k3=d(2).toString Into grp=Group
Let cs = Enumerable.Range(3, dtData.Columns.Count - 3)
Let cvl = cs.Select(Function (x) grp.Select(Function (i) i(x)).toArray)
Let cvc = cvl.Select(Function (v) String.Join(",", v.Where(Function (e) Not (IsNothing(e) OrElse String.IsNullOrEmpty(e.toString)))))
Let ra = (New Object(){k1,k2,k3}).Concat(cvc).ToArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

[MichaelL]

You are amazing. Thank you so much!

[poorna_nayak07 (Poornachandra M)]

Hi,

Here is the updated linq expression.
It is similar to the one provided by @ppr. I have removed enumerating over Column range and added actual columns itself.

(From d In TestDT.AsEnumerable
Group d By k1=d(0).toString.Trim,k2=d(1).toString.Trim,k3=d(2).toString.Trim Into grp=Group
Let upArr=TestDt.Columns.Cast(Of DataColumn).skip(3).Select(Function (col) String.Join(",", grp.Select(Function (x) x(col.ColumnName)))).ToList
Let ra = (New Object(){k1,k2,k3}).Concat(upArr).ToArray
Select ResDT.Rows.Add(ra)).CopyToDataTable

[ppr (Peter Preuss)]

@poorna_nayak07
you will get e.g. for the B Group
",,,,,," for Column Expense: Loyali… Right?

[poorna_nayak07 (Poornachandra M)]

Ooh yes. I prepared this linq without empty cells in my Datatable Variable

How about below one

(From d In TestDT.AsEnumerable
Group d By k1=d(0).toString.Trim,k2=d(1).toString.Trim,k3=d(2).toString.Trim Into grp=Group
Let upArr=TestDt.Columns.Cast(Of DataColumn).skip(3).Select(Function (col) String.Join(",", grp.Where(Function(x) Not(String.IsNullOrEmpty(x(col.ColumnName).tostring))).Select(Function (x) x(col.ColumnName)))).ToList
Let ra = (New Object(){k1,k2,k3}).Concat(upArr).ToArray
Select ResDT.Rows.Add(ra)).CopyToDataTable

[MichaelL]

Hi Peter, what if i’ve got different months for the same Property?

[ppr (Peter Preuss)]

By your current definition it will be a another Group

AUS A JUN
AUS A SEP

if it is needed differently then collect all requirements with new sample data and let us know

[MichaelL]

Hi Peter,

Thank you for the prompt response. The requirements would be like this.

Before:

After: