从数据表创建数据透视表 [英] Create a Pivot Table from a DataTable

问题描述

我使用C＃的WinForms创建需要把一个DataTable到透视表的应用程序。我有透视表从SQL年底做工精细，而是从一个DataTable创建它似乎棘手。我似乎无法找到内置.NET为这样的东西。

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.

请注意：我确实有我操纵之前创建透视数据从一个侧面.NET做到这一点。

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.

*我有一个数据表与列的startDateTime，龙头和数据。的startdates应一起分组和数据值平均（每STARTDATE有时多于一个数据值）。该表如下所示：

*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?

编辑：忘了提，这些抽头值是1-4并非总是如此，他们在数量和价值各不相同。

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

推荐答案

这样的透视表可以用免费的 NReco.PivotData 聚集库：

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

行和列密钥由pvtTbl.RowKeys和pvtTbl.ColumnKeys集合代表;值/总计可以通过索引被访问（例如： pvtTbl [0,0] .value的）或行+列键（例如： pivotData [新密钥（新的DateTime（2012，3，30，11，42，00）），新的密钥（4）]。值）。

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

这篇关于从数据表创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！