Pivoting values from datatable

Question

I have a datatable in the following form

Country Currency    Category    Value (Delivered)
AFRICA  USD         CONTAINER   100
AFRICA  USD         CONTAINER   100
AFRICA  USD         PLASTIC     100 
AFRICA  USD         PLASTIC     100 
AFRICA  USD         PLASTIC     100 
AFRICA  USD         PLASTIC     100 
AFRICA  USD         PLASTIC     100
AFRICA  USD         PLASTIC     100

I need to get the values as below,

COUNTRY Currency    CONTAINER   PLASTIC     OTHERS
Africa  USD         200         600          0

Means, anything other than plastic or container, need to be added in others column.

Answer 1

You can use NReco.PivotData nuget package for this purpose:

DataTable tbl;  // lets assume this is your datatable
var pvtData = new PivotData(new[] {"Country", "Currency", "Category"}, new SumAggregatorFactory("Value (Delivered)") );
pvtData.ProcessData(new DataTableReader(tbl));
var pvtTbl = new PivotTable(
    new [] {"Country", "Currency"}, //rows
    new [] {"Category"}, //columns
    pvtData);

// use PivotTable class API to iterate through rows/columns and get the values
for (var r=0; r<pvtTbl.RowKeys.Length; r++) { 
    var rowKey = pvtTbl.RowKeys[r];
    for (var c=0; c<pvtTbl.ColumnKeys.Length; c++) {
        var cellValue = pvtTbl[r,c].Value;
        // do what you need: render HTML table, or export to CSV, or populate pivoted DataTable
    }
}

Library is free for single-deployment non-SaaS projects (I'm the author of this lib).