Saturday, December 25, 2004

Creating reports using Pivot operator

In this article let me try and compare the way to create cross tab reports in SQL Server 2000 and SQL Server 2005.

Cross Tab Report: Representing columns as Rows and Rows as Columns is known as cross tab report or PivotTable.

Sample Table Structure and Data

Create table TestPivot
(
YearOfSales Int,
SalesQuarter Int,
Amount money
)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, 100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, 200)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, 300)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, 400)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, 500)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, 600)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, 700)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, 800)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 1, 900)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 2, 1000)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 3, 1100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 4, 1200)
go

The below query is to create Cross tab report OR PIVOT table in SQL 2000. As expected it would work in SQL 2005 also.

Select YearOfSales,
sum(case SalesQuarter when 1 then Amount else 0 end) as Q1,
sum(case SalesQuarter when 2 then Amount else 0 end) as Q2,
sum(case SalesQuarter when 3 then Amount else 0 end) as Q3,
sum(case SalesQuarter when 4 then Amount else 0 end) as Q4
from TestPivot
group by YearOfSalesgo

The below query is to create Cross tab report OR PIVOT table in SQL 2005

Select * from TestPivotPIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt

2 comments:

Anonymous said...

hello
when i try to execute ur statements Creation of tables is suceess full
but
eror displayed while executing Pivot creating atatements Sql 2005


Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'for'.

LostInParadise? said...

He's missing a space between "pivot" and "PIVOT"....

Select * from TestPivot PIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt