I want to group by on one column in a C# datatable as below.

Question

Want to group by on one key column(RefPI) in a C# datatable.My table currently is: RefPI Date1 Date2 Status1 Status2 Status3 C2-EP1 d1 d2 C2-EP2 d3 d4 C2-EP3 d6 d7 C2-EP1 OK OK No C2-EP2 ok st1 DATA1 C2-EP2 Good yes yes //Expected result RefPI Date1 Date2 Status1 Status2 Status3 C2-EP1 d1 d2 OK OK No C2-EP2 d3 d4 ok st1 DATA1 Good yes yes C2-EP3 d6 d7 Above I wanted to group on C2-EP1,C2-EP2,C2-EP3.

Gajendra Jangid · Answer

hello dear, try this code: var grouped = from table in dt.AsEnumerable() group table by new { placeCol = table[ "Place" ] } into groupby select new { Value = groupby.Key, ColumnValues = groupby };

A c · Answer

Thanks a lot!! it worked and I tweaked to satisfy my need as below: public static class Program { static void Main( string [] args) { DataTable dtPI = new DataTable(); dtPI.Columns.Add( "RefPI" , typeof ( string )); dtPI.Columns.Add( "date1" , typeof ( string )); dtPI.Columns.Add( "date2" , typeof ( string )); dtPI.Columns.Add( "status1" , typeof ( string )); dtPI.Columns.Add( "status2" , typeof ( string )); dtPI.Columns.Add( "status3" , typeof ( string )); dtPI.Columns.Add( "status4" , typeof ( string )); dtPI.Rows.Add( "C2-EP1" , "22/02" , "24/05" , "" , "" , "" ); dtPI.Rows.Add( "C2-EP2" , "22/02" , "24/05" , "" , "" , "" ); dtPI.Rows.Add( "C2-EP3" , "22/02" , "24/05" , "" , "" , "" ); dtPI.Rows.Add( "C2-EP1" , "" , "" , "OK" , "OK" , "" ); dtPI.Rows.Add( "C2-EP2" , "" , "" , "OK" , "OK" , "Data1" ); dtPI.Rows.Add( "C2-EP2" , "" , "" , "yes" , "yes" , "Yes" ); var grouped = from tabl in dtPI.AsEnumerable() group tabl by new { placeCol =tabl[ "RefPI" ] } into groupby select new { value = groupby.Key, ColumnValues = groupby }; DataTable dtCreateRowColum = new DataTable(); string keyCEP = "" ; int countCEP = 0; foreach (var key in grouped) { if (!dtCreateRowColum.Columns.Contains( "RefPI" )) dtCreateRowColum.Columns.Add( "RefPI" , typeof ( string )); Console.WriteLine(key.value.placeCol); keyCEP = Convert.ToString(key.value.placeCol); Console.WriteLine( "---------------------------" ); foreach (var columnValue in key.ColumnValues) { if (!dtCreateRowColum.Columns.Contains( "date1" )) dtCreateRowColum.Columns.Add( "date1" , typeof ( string )); if (!dtCreateRowColum.Columns.Contains( "date2" )) dtCreateRowColum.Columns.Add( "date2" , typeof ( string )); if (!dtCreateRowColum.Columns.Contains( "status1" )) dtCreateRowColum.Columns.Add( "status1" , typeof ( string )); if (!dtCreateRowColum.Columns.Contains( "status2" )) dtCreateRowColum.Columns.Add( "status2" , typeof ( string )); if (!dtCreateRowColum.Columns.Contains( "status3" )) dtCreateRowColum.Columns.Add( "status3" , typeof ( string )); if (!dtCreateRowColum.Columns.Contains( "status4" )) dtCreateRowColum.Columns.Add( "status4" , typeof ( string )); if (countCEP==0) dtCreateRowColum.Rows.Add(keyCEP, columnValue[ "date1" ].ToString(), columnValue[ "date2" ].ToString(), columnValue[ "status1" ].ToString(), columnValue[ "status2" ].ToString(), columnValue[ "status3" ].ToString(), columnValue[ "status4" ].ToString()); else dtCreateRowColum.Rows.Add( "" , columnValue[ "date1" ].ToString(), columnValue[ "date2" ].ToString(), columnValue[ "status1" ].ToString(), columnValue[ "status2" ].ToString(), columnValue[ "status3" ].ToString(), columnValue[ "status4" ].ToString()); Console.WriteLine(columnValue[ "date1" ].ToString()); Console.WriteLine(columnValue[ "date2" ].ToString()); Console.WriteLine(columnValue[ "status1" ].ToString()); Console.WriteLine(columnValue[ "status2" ].ToString()); Console.WriteLine(columnValue[ "status3" ].ToString()); Console.WriteLine(columnValue[ "status4" ].ToString()); countCEP++; } countCEP = 0; Console.WriteLine(); } Console.ReadLine(); } You are the champ!!

A c · Answer

Thanks for your reply. I will try and let you know