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
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
Comments
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'.
Select * from TestPivot PIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt