在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

推荐答案

帮助这里 <一href="http://msdn.microsoft.com/en-us/library/aa172756%28SQL.80%29.aspx">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

另一个输出：

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

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