Pivot Table in c#

Question

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

are you trying to create an excel pivot table or are you trying to display some kind of pivoted data in a GridView? — Jimmy, Jul 01 '09 at 15:12
What is the data source? If some form of SQL, I'd suggest that you do this at SQL. Any possible chance that you could use LINQ? — Frank V, Jul 01 '09 at 15:31
ah I wish I could use linq. we are still on .net 2.0. I don't have control on SQL side so basically it would have to be done by playing with data table. — Sheraz, Jul 01 '09 at 15:43
Any progress with this? I've implemented pivot tables from scratch in Java. I can walk you through the details if you're still looking for information. — Mark Bolusmjak, Nov 08 '09 at 10:40

score 6 · Answer 1 · edited Sep 17 '11 at 22:30

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

score 2 · Answer 2 · answered Jul 01 '09 at 15:36

2

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.

The usual disparaging remarks about MS-Access don't apply here because you aren't actually storing data in MS-Access.

answered Jul 01 '09 at 15:36

MatthewMartin

32,326
33
105
164

any chance to post an example here – Sheraz Jul 01 '09 at 15:51
Transform command: http://msdn.microsoft.com/en-us/library/bb208956.aspx Using MS-Access via .NET http://www.csharphelp.com/archives/archive70.html And the rest involves link tables your favorite views or doing a passthrough query/stored proc for for source data : http://www.aspfree.com/c/a/Microsoft-Access/On-Using-Passthrough-Queries-in-MS-Access/ – MatthewMartin Jul 01 '09 at 16:38

score 0 · Answer 3 · edited Sep 17 '11 at 22:59

CellSetGrid is an Open Source ASP .Net (c#) control, which offers pivot table like functionality.

This used to be available for download in this site: http://www.SQLServerAnalysisServices.com

Now the site does not host this control anymore. So I have uploaded the source of the control - CellSetGrid here.

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

Pivot Table in c#

3 Answers3

Linked