C#中的数据透视表 [英] Pivot Table in c#

问题描述

我需要在 .net 中创建一个数据透视表.不能使用任何第三方控件(除非它是免费的).我试图找到说明如何创建数据透视表(算法或步骤)的文档，但几乎所有内容都与 excel 有关.有谁知道如何在 C# 中创建数据透视表???谢谢

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

推荐答案

这里有帮助http://msdn.microsoft.com/en-us/library/aa172756%28SQL.80%29.aspx

实际表:

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  

期望的输出:(这里 Q 代表季度)

Desired Output: (Here Q for Quarter)

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  

查询:

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

另一个输出:

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

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