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.

Miry

Would this work: codeproject.com/Articles/46486/Pivoting-DataTable-Simplified – Fustic

Spent the whole morning clowning around with it and couldn't get it working -- thanks for convincing me to try again! Works now :) – Miry

There's a tutorial and a nicely wrapped class available here. – Bushwa

Answer 1

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

Alden

it would be great if you add some comment to understand what's happening here – Pilewort

The row is added to dictionary as a reference. So after the row is created and added to the dictionary, the data is added to the DataTable via the row in the dictionary. – Arne

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 ).

Sioux

I am pretty sure he was asking how to do it himself and not use a library. The library is very good but not the answer. – Mufinella

Answer 3

You can try with the function below (C# code):

Usage:

Notice you can add as many columns you need in the second argument.

DataTable pivot = PivotTable(t, "Tap", "Data");

Function:

public static DataTable PivotTable(DataTable table, string pivotColumn, params string[] includeColumns)
{
    if (table.Columns.IndexOf(pivotColumn) == -1)
        throw new Exception(string.Format("PivotColumn {0} could not be found.", pivotColumn));

    // Clone the table
    DataTable t = table.Clone();
    foreach (DataRow r in table.Rows)
        t.Rows.Add(r.ItemArray);

    DataTable distinctValues = t.DefaultView.ToTable(true, pivotColumn);
    int maxRowIndex = 0;
    for (int i = 0; i < distinctValues.Rows.Count; i++)
    {
        // Add rows as columns 
        DataRow row = distinctValues.Rows[i];
        string value = row[pivotColumn].ToString();

        if(includeColumns != null)
        {
            for (int j = 0; j < includeColumns.Length; j++) 
            {
                if (t.Columns.IndexOf(includeColumns[j]) == -1)
                    continue;
                t.Columns.Add(new DataColumn(string.Concat(value, includeColumns[j]), t.Columns[includeColumns[j]].DataType));
            }                        
        }

        // Fill new columns
        DataRow[] select = t.Select(string.Format("{0}='{1}'", pivotColumn, value));
        maxRowIndex = Math.Max(maxRowIndex, select.Length);
        for (int j = 0; j < select.Length; j++)
        {
            DataRow r = select[j];
            if (includeColumns != null)
            {
                for (int k = 0; k < includeColumns.Length; k++)
                {
                    if (t.Columns.IndexOf(includeColumns[k]) == -1)
                        continue;

                    string colName = string.Concat(value, includeColumns[k]);
                    t.Rows[j][colName] = r[includeColumns[k]];
                }
            }
        }
    }

    // Remove original columns
    t.Columns.Remove(pivotColumn);
    foreach (string c in includeColumns)
        t.Columns.Remove(c);

    // Remove pivoted rows
    if(maxRowIndex > 0)
    {
        while (maxRowIndex < t.Rows.Count)
            t.Rows.RemoveAt(maxRowIndex);
    }
    return t;
}

Hypogene

Answer 4

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

Tagliatelle

Answer 5

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

Welldisposed