Create pivot table from a DataTable PivotData C# Examples

NReco.PivotData can be used to pivot a DataTable and efficiently calculate aggregates in C# code.

The following C# code snippet illustrates how to pivot a DataTable and group by multiple columns with NReco PivotData library:

DataTable tbl; // lets assume this table has "product" and "year" columns

// group data by specified columns and calculate aggregates
var pvtData = new PivotData(new[] {"product", "year" }, new CountAggregatorFactory() );
pvtData.ProcessData(new DataTableReader(tbl));

var pvtTbl = new PivotTable(
    new [] {"product"}, //rows
	new [] {"year"}, //columns
	pvtData);

PivotData class provide simple API for accessing aggregated values (see cube basics for more details):

Console.WriteLine("Product 'Product1' in 2015 total sum: {0}", pvtData["product",2015].Value );
Console.WriteLine("Product 'Product1' total sum: {0}", pvtData["product",null].Value );
Console.WriteLine("Total sum: {0}", pvtData[null,null].Value ); // grand total

Instead of "count" other aggregate functions may be used (sum, average, min, max etc).
Note: you can process data directly from IDataReader, without loading whole dataset into memory.

Pivot table model may be exported to new DataTable with PivotTableDataTableWriter (Toolkit component):

var dataTableWr = new PivotTableDataTableWriter("Test");
DataTable tbl = dataTableWr.Write(pvtTbl);

If dataset is too large for processing on the .NET side (>1 mln rows) it is possible to peform data grouping on database level and load pre-aggregated data into PivotData for further analytical processing or pivot tables generation.