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

Learn the hash-pivot tesuji:

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);
  }
}

Answer 2

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 3

Maybe this will help you. It is in vb.net.

Public Function pivot_datatable(ByVal datatable_source As DataTable, ByVal datacolumn_rows As DataColumn(), ByVal datacolumn_columns As DataColumn, ByVal datacolumn_value As DataColumn) As DataTable
    Dim temp_datacolumn As DataColumn
    Dim current_datarow As DataRow
    Dim datarow_destination As DataRow = Nothing
    Dim current_column_name As String = ""
    Dim primary_key() As DataColumn = New DataColumn() {}
    Dim key_columns() As Object
    Dim newOrdinal As Integer
    Dim i As Integer
    Dim sort_string As String = ""

    Try
        pivot_datatable = New DataTable()

        For Each temp_datacolumn In datatable_source.Columns
            If temp_datacolumn.Ordinal <> datacolumn_columns.Ordinal AndAlso temp_datacolumn.Ordinal <> datacolumn_value.Ordinal Then
                array_insert(primary_key, pivot_datatable.Columns.Add(temp_datacolumn.ColumnName, temp_datacolumn.DataType))
                sort_string &= temp_datacolumn.ColumnName & " ASC, "
            End If
        Next
        pivot_datatable.PrimaryKey = primary_key

        For Each current_datarow In datatable_source.Rows ' Main Process to add values to pivot table
            current_column_name = current_datarow(datacolumn_columns.Ordinal).ToString
            If Not pivot_datatable.Columns.Contains(current_column_name) Then ' Column is new
                temp_datacolumn = pivot_datatable.Columns.Add(current_column_name, datacolumn_value.DataType)
                newOrdinal = temp_datacolumn.Ordinal
                For i = newOrdinal - 1 To datatable_source.Columns.Count - 2 Step -1
                    If temp_datacolumn.ColumnName.CompareTo(pivot_datatable.Columns(i).ColumnName) < 0 Then
                        newOrdinal = i
                    End If
                Next
                temp_datacolumn.SetOrdinal(newOrdinal)
            End If

            key_columns = New Object() {}
            For Each data_column As DataColumn In datacolumn_rows
                array_insert(key_columns, current_datarow(data_column.Ordinal).ToString)
            Next data_column
            datarow_destination = pivot_datatable.Rows.Find(key_columns)
            If datarow_destination Is Nothing Then ' New Row
                datarow_destination = pivot_datatable.NewRow()
                For Each temp_datacolumn In datatable_source.Columns
                    If temp_datacolumn.Ordinal <> datacolumn_columns.Ordinal AndAlso temp_datacolumn.Ordinal <> datacolumn_value.Ordinal Then
                        datarow_destination(temp_datacolumn.ColumnName) = current_datarow(temp_datacolumn.ColumnName)
                    End If
                Next
                pivot_datatable.Rows.Add(datarow_destination)
            End If
            datarow_destination(current_column_name) = current_datarow(datacolumn_value.Ordinal)
        Next

        Return sort_datatable(pivot_datatable, sort_string.Substring(0, sort_string.Length - 2))
    Catch ex As Exception
        Return Nothing
    End Try
End Function

Answer 4

Another small piece of code to pivot any table you would want:

        var dataTable = new DataTable(); // your input DataTable here!
        var pivotedDataTable = new DataTable(); //the pivoted result
        var firstColumnName = "Year";
        var pivotColumnName = "Codes";

        pivotedDataTable.Columns.Add(firstColumnName);

        pivotedDataTable.Columns.AddRange(
            dataTable.Rows.Cast<DataRow>().Select(x => new DataColumn(x[pivotColumnName].ToString())).ToArray());

        for (var index = 1; index < dataTable.Columns.Count; index++)
        {
            pivotedDataTable.Rows.Add(
                new List<object> { dataTable.Columns[index].ColumnName }.Concat(
                    dataTable.Rows.Cast<DataRow>().Select(x => x[dataTable.Columns[index].ColumnName])).ToArray());
        }