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

问题描述

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

注意：在创建支点之前，我必须在.NET边操作数据。

$ b $我已经阅读了一些与此类似的事情的文章，但是我很难将其应用于我的问题。

*我有一个包含列StartDateTime，Tap和Data的数据。 startdates应该分组在一起，数据值平均（有时每个startdate多于一个数据值）。表格如下所示：

数据透视表应该像下面的图像一样输出（不是舍入值）。列号是不同的抽头号码（每个唯一号码一个）。

如何从datatable创建这个数据透视表？

编辑：忘记提及，这些tap值不总是从1-4开始，它们的数量和价值都有所不同。

解决方案

了解hash-pivot tesuji：

  var oDT = new数据表
 var dfq = new Dictionary< DateTime，DataRow> ;; 
 oDT.Columns.Add（StartDateTime，typeof DateTime）; （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）; 
} 
} 
  

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.

解决方案

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

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