Create a Pivot Table from a DataTable

Question

I am using C# winforms to create an application that needs to turn a datatable into a pivot table. I have the pivot table working fine from a SQL end, but creating it from a datatable seems trickier. I couldn't seem to find anything built into .NET for this.

NOTE: I do have to do this from a .NET side as I manipulate the data prior to creating the pivot.

I've read through some articles that did some similar things as this, but I've had difficultly applying them to my problem.

*I have a datatable with the columns "StartDateTime", "Tap", and "Data". The startdates should be grouped together and data values averaged (sometimes more than one data value per startdate). The table is shown below:

Pivot table should output like the image below (not rounded values though). The column numbers are the distinct tap numbers (one for each unique one).

How can I go about creating this pivot table from the datatable?

EDIT: forgot to mention, these tap values are not always from 1-4, they do vary in number and value.

Answer 1

Pivot table like that can be easily calculated with free NReco.PivotData aggregation library:

DataTable t;  // assume it has: StartDateTime, Data, Tap
var pivotData = new PivotData(
    new string[] {"StartDateTime","Tap"},
    new AverageAggregatorFactory("Data"),
    new DataTableReader(t) );
var pvtTbl = new PivotTable(
    new [] {"StartDateTime"},  // row dimension(s)
    new [] {"Tap"}, // column dimension(s),
    pivotData);

Row and column keys are represented by pvtTbl.RowKeys and pvtTbl.ColumnKeys collections; values / totals could be accessed by indexer (for example: pvtTbl[0,0].Value ) or by row+column key (for example: pivotData[new Key(new DateTime(2012, 3, 30, 11, 42, 00)), new Key(4)].Value ).

Answer 2

Learn the hash-pivot tesuji:

var inDT = new DataTable();
// Fill the input table

var oDT = new DataTable();
var dfq = new Dictionary<DateTime, DataRow>;
oDT.Columns.Add("StartDateTime", typeof(DateTime));
for (int i = 0; i < inDT.Rows.Count; i++) {
    var key = (DateTime)inDT.Rows[i][0];
    var row = (String)inDT.Rows[i][2];
    var data = (Double)inDT.Rows[i][1];

    if (!oDT.Columns.Contains(row)) {
       oDT.Columns.Add(row);
    }
    if (dfq.ContainsKey(key)) {
        dfq[key][row] = data;
    } else {
        var oRow = oDT.NewRow();
        oRow[0] = key;
        oRow[row] = data;
        dfq.Add(key, oRow);
        oDT.Rows.Add(oRow);
    }
}
// pivot table in oDT