Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pivot Table in c#

Tags:

c#

ado.net

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

like image 250
Sheraz Avatar asked Jul 01 '09 15:07

Sheraz


People also ask

What is pivot table in C#?

Introduction. Pivot transformation is very useful to summarize data in a flat data table (columns and rows), providing a more clean visualization of the data. In this article, we will see two methods to retrieve Pivot and Inverted tables from a DataTable .

What does a pivot table include?

A pivot table usually consists of row, column and data (or fact) fields. In this case, the column is ship date, the row is region and the data we would like to see is (sum of) units. These fields allow several kinds of aggregations, including: sum, average, standard deviation, count, etc.

What data is pivot table using?

A Pivot Table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them.

How does a pivot table works?

When you drag a field into the Values area, the pivot table will automatically sum or count the data in that field. If the data in the field contains numbers, then the sum will be calculated. If the data contains text or blanks, then the count will be calculated.


1 Answers

Helping here 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)

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
like image 196
3 revs Avatar answered Oct 07 '22 06:10

3 revs