c# - "Collapse" and sum data table -

i have pivoted data table columns locations , there around 100. data little strange , i'm looking easy way sum (or collapse data).

date, location 1, location 2, location 3 1/1/2001, 6, 0, 0 2/1/2001, 10, 0, 0 1/1/2001, 0, 5, 0 2/1/2001, 0, 4, 0 1/1/2001, 0, 0, 8 2/1/2001, 0, 0, 2 

so can see there 0 fillers if sum i'd unique list of dates , "collapsed" result has no 0 fillers.

again, have 100 columns , can't hardcoded need way sum these columns dynamically. there trick this? maybe in linq?

i use combination of loops , linq:

datatable pivotedtable = table.clone(); // same columns, empty var pivotcolumns = pivotedtable.columns.cast<datacolumn>().skip(1).tolist();  var dategroups = table.asenumerable()     .groupby(r => r.field<datetime>("date").date); foreach(var date in dategroups) {     datarow row = pivotedtable.rows.add(); // added table     row.setfield("date", date.key);     foreach(datacolumn c in pivotcolumns)         row.setfield(c, date.sum(r => r.field<int>(c.columnname))); } 

here's sample data:

datatable table = new datatable(); table.columns.add("date", typeof(datetime)); table.columns.add("location 1", typeof(int)); table.columns.add("location 2", typeof(int)); table.columns.add("location 3", typeof(int)); table.rows.add(new datetime(2001, 1, 1), 6, 0, 0); table.rows.add(new datetime(2001, 2, 1), 10, 0, 0); table.rows.add(new datetime(2001, 1, 1), 0, 5, 0); table.rows.add(new datetime(2001, 2, 1), 0, 4, 0); table.rows.add(new datetime(2001, 1, 1), 6, 0, 8); table.rows.add(new datetime(2001, 1, 1), 6, 0, 2); 

output:

date                    location 1  location 2  location 3 01.01.2001 00:00:00      18          5           10 01.02.2001 00:00:00      10          4           0