Aggregate datatable with dynamic number of columns

Question

I have a datatable with dynamic set of columns and want to aggregate the numeric based columns and keep the final rows into new datatable.

DataTable Sample:-

PartnerName   CreditCol  DebitCol  AmountCol ....
P1              10       20        30 
P2              1        2         3 
P3              3        1         10
P2              1        100       200

The desired output should be :-

PartnerName   CreditCol  DebitCol  AmountCol ....
P1              10       20        30 
P2              2        102       203 
P3              3        1         10

The main thing here is the column set and will be dynamic. Sometime, there could be two columns and sometimes it could be 20 cols. Please suggest the linq query or any other solution.

Counter

Is the type of the columns that you want to aggregate always int? Is the column you want to group-by always one column or can it be a combination of muultiple columns? Have you tried anything? – Impenitent

The primary key will be based on one column i.e. PartnerName. For numeric column, it could be string. But we have to cast it to int for aggregation. – Counter

and you want it to check every column if all values in all rows can be parsed to int? Can't you provide the columns that you want to sum? What if a column contains "10.0"? That's clearly not an int but it could be parsed to decimal which could be converted to an int or summed to a decimal value with decimal places. – Impenitent

I tried to perform with the datarow iteration but it was getting complex. I am new to Linq and may be its easy to manipulate in the linq query. – Counter

It will always contain integer data, nothing to worry for decimals. But the column set could vary. We can skip the parsing check if it is a pricey evaluation.. – Counter

Answer 1

Here is a dynamic approach that should work for your requirement:

var rows = table.AsEnumerable();
var columns = table.Columns.Cast<DataColumn>();
int i;  // used to check if a string column can be parsed to int
string columnToGroup = "partnername";
DataColumn colToGroup = columns.First(c => c.ColumnName.Equals(columnToGroup, StringComparison.OrdinalIgnoreCase));
var colsToSum = columns
     .Where(c => c != colToGroup &&
         (c.DataType == typeof(int) ||
         (c.DataType == typeof(string)
         && rows.All(r => int.TryParse(r.Field<string>(c), out i)))));
var columnsToSum = new HashSet<DataColumn>(colsToSum);

DataTable tblSum = table.Clone(); // empty table, same schema
foreach (var group in rows.GroupBy(r => r[colToGroup]))
{
    DataRow row = tblSum.Rows.Add();
    foreach(var col in columns)
    {
        if (columnsToSum.Contains(col))
        {
            int sum;
            if (col.DataType == typeof(int))
                sum = group.Sum(r => r.Field<int>(col));
            else
                sum = group.Sum(r => int.Parse(r.Field<string>(col)));
            row.SetField(col.ColumnName, sum);
        }
        else
            row[col.ColumnName] = group.First()[col];
    }
}

Tested with your sample data here:

var table = new System.Data.DataTable();
table.Columns.Add("PartnerName", typeof(string));
table.Columns.Add("CreditCol", typeof(int));
table.Columns.Add("DebitCol", typeof(string));
table.Columns.Add("AmountCol", typeof(int));
table.Rows.Add("P1", 10, "20", 30);
table.Rows.Add("P2", 1, "2", 3);
table.Rows.Add("P3", 3, "1", 10);
table.Rows.Add("P2", 1, "100", 200);

Result:

PartnerName   CreditCol    DebitCol    AmountCol
P1            10             20         30
P2            2              102        203
P3            3              1          10

Impenitent

Thanks for the soln, Let me try and keep you posted. – Counter