C# 从数据表创建数据透视表

Create a Pivot Table from a DataTable

提问by ImGreg

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.

我正在使用 C# winforms 创建一个需要将数据表转换为数据透视表的应用程序。我让数据透视表从 SQL 端正常工作，但从数据表创建它似乎更棘手。我似乎找不到任何内置到 .NET 中的东西。

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

注意：我必须从 .NET 端执行此操作，因为我在创建数据透视图之前操作数据。

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:

*我有一个包含“StartDateTime”、“Tap”和“Data”列的数据表。开始日期应组合在一起，并对数据值取平均值（有时每个开始日期有多个数据值）。该表如下所示：

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.

编辑：忘了提，这些抽头值并不总是从 1-4，它们的数量和值确实不同。

回答by Joshua

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

回答by Thomas Papageorgiou

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

也许这会帮助你。它在 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

回答by Vitaliy Fedorchenko

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

可以使用免费的NReco.PivotData聚合库轻松计算这样的数据透视表：

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

行键和列键由 pvtTbl.RowKeys 和 pvtTbl.ColumnKeys 集合表示；值/总计可以通过索引器（例如：）pvtTbl[0,0].Value或行+列键（例如：）访问pivotData[new Key(new DateTime(2012, 3, 30, 11, 42, 00)), new Key(4)].Value。

回答by Andrei Olaru

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