The solution 1 given by
Shmuel Zang is good. But, I think the code as it may not work for
DataTable
.
In the question it is stated that table is generated from query, I suppose it is a
DataTable
, and the result required is also formulated in the question in the same format of the input table. For this case the following code can be used to
Group
the data in the
DataTable
and then aggregate the values and finally to create the result as a
DataTable
.
I have added April month values to illustrate grouping.
void Main()
{
DataTable itemDetails = new DataTable();
itemDetails.Columns.Add("Id",typeof(int),null);
itemDetails.Columns.Add("Item",typeof(string),null);
itemDetails.Columns.Add("Date",typeof(DateTime),null);
itemDetails.Columns.Add("Amount",typeof(int),null);
itemDetails.Rows.Add(1, "Item1",new DateTime(2012,03,24), 25);
itemDetails.Rows.Add(1, "Item2",new DateTime(2012,03,24), 25);
itemDetails.Rows.Add(1, "Item3",new DateTime(2012,03,24), 25);
itemDetails.Rows.Add(1, "Item1",new DateTime(2012,03,25), 25);
itemDetails.Rows.Add(1, "Item1",new DateTime(2012,03,26), 25);
itemDetails.Rows.Add(1, "Item2",new DateTime(2012,03,27), 25);
itemDetails.Rows.Add(1, "Item3",new DateTime(2012,03,28), 25);
DataTable monthValues = new DataTable();
monthValues.Columns.Add("Month", typeof(string),null);
monthValues.Columns.Add("Item1", typeof(int),null);
monthValues.Columns.Add("Item2", typeof(int),null);
monthValues.Columns.Add("Item3", typeof(int),null);
monthValues.Columns["Item1"].DefaultValue=0;
monthValues.Columns["Item2"].DefaultValue=0;
monthValues.Columns["Item3"].DefaultValue=0;
itemDetails.AsEnumerable().Select (row => {
DataRow mvRow = monthValues.NewRow();
mvRow["Month"]=row.Field<DateTime>("Date").ToString("MMM");
mvRow[row.Field<string>("Item")]=row.Field<int>("Amount");
return mvRow;
}).CopyToDataTable(monthValues,LoadOption.OverwriteChanges);
DataTable totalTable = monthValues.AsEnumerable().GroupBy (row =>row.Field<string>("Month"))
.Select (row => {DataRow totalRow = monthValues.NewRow();
totalRow["Month"]=row.Key;
totalRow["Item1"]=row.Sum (r => r.Field<int>("Item1"));
totalRow["Item2"]=row.Sum (r => r.Field<int>("Item2"));
totalRow["Item3"]=row.Sum (r => r.Field<int>("Item3"));
return totalRow;}).CopyToDataTable();
foreach(DataColumn col in monthValues.Columns)
Console.Write(col.ColumnName + "\t");
Console.WriteLine ();
foreach(DataRow row in totalTable.Rows){
foreach (var element in row.ItemArray)
{
Console.Write(element.ToString() + "\t");
}
Console.WriteLine ();
}
}