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

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!!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gajendra Jangid7y ago
Accepted 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
};
0
A c7y ago
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!!
0
A c7y ago
Thanks for your reply. I will try and let you know
0