C#中的数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1069677/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot Table in c#
提问by Sheraz
I need to create a pivot table in .net. Can't use any third party control (unless it's free). I tried to find documentation that explains how to create pivot table (algorithm or steps) in general but almost everything is related to excel. Does anyone know how to create pivot table in c#??? Thanks
我需要在 .net 中创建一个数据透视表。不能使用任何第三方控件（除非它是免费的）。我试图找到解释如何创建数据透视表（算法或步骤）的文档，但几乎所有内容都与 excel 相关。有谁知道如何在 C# 中创建数据透视表？？？谢谢
回答by MatthewMartin
MS-Access has the TRANSFORM command (which does a pivot), so you could use ADO.NET to query a ms-access mdb file, then use passthrough queries there to get to the data source that can't pivot (usually MS-SQL/T-SQL). I did a proof of concept of this and it worked and was about 5000 LOC shorter than the VBScript implementation that did the pivot using arrays.
MS-Access 具有 TRANSFORM 命令（执行数据透视），因此您可以使用 ADO.NET 查询 ms-access mdb 文件，然后在那里使用直通查询来获取无法透视的数据源（通常是 MS- SQL/T-SQL）。我对此做了一个概念证明，它有效并且比使用数组进行数据透视的 VBScript 实现短了大约 5000 LOC。
The usual disparaging remarks about MS-Access don't apply here because you aren't actually storing data in MS-Access.
通常关于 MS-Access 的贬低言论不适用于这里，因为您实际上并未在 MS-Access 中存储数据。
回答by Md. Sumonur Rahman
Helping here http://msdn.microsoft.com/en-us/library/aa172756%28SQL.80%29.aspx
在这里帮助 http://msdn.microsoft.com/en-us/library/aa172756%28SQL.80%29.aspx
Actual Table:
实际表：
Year Quarter Amount
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
1992 4 2.4
Desired Output: (Here Q for Quarter)
所需的输出：（此处为 Q 季度）
Year Q-1 Q-2 Q-3 Q-4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
1992 0.0 0.0 0.0 2.4
Query:
询问：
Use Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
INSERT INTO Pivot VALUES (1992, 4, 2.4)
GO
SELECT * FROM Pivot
GO
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Another Output:
另一个输出：
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
回答by Jeganinfo
CellSetGrid is an Open Source ASP .Net (c#) control, which offers pivot table like functionality.
CellSetGrid 是一个开源 ASP .Net (c#) 控件，它提供类似于数据透视表的功能。
This used to be available for download in this site: http://www.SQLServerAnalysisServices.com
这曾经可以在以下站点下载：http: //www.SQLServerAnalysisServices.com
Now the site does not host this control anymore. So I have uploaded the source of the control - CellSetGrid here.
现在该站点不再托管此控件。CellSetGrid -因此，我已上传的源头控制这里。
- You can build the source
- Add this as a Control in Visual Studio toolbox.
- Drag and Drop control to a web form
- Set the connection string to the cube
- This will show all the dimensions and measure groups so you can drag n drop what you want to get a pivot table like functionality
- 您可以构建源
- 将此添加为 Visual Studio 工具箱中的控件。
- 将控件拖放到 Web 表单
- 将连接字符串设置为多维数据集
- 这将显示所有维度和度量组，因此您可以拖放您想要的数据透视表，例如功能
