Friday, February 06, 2009
SQL Server 2005 has lots of new features, of which one is PIVOT clause. This is similar to Pivot tables in MS Excel and Matrix Control in Reporting Service. Here is a simple example demonstrating how to use PIVOT clause. I am right now taking a simple example. There are scenarios where there could be a need to write a big stored procedure to build a Pivot table in lower versions of SQL Server.
Do run the below query in Query Analyzer to create the table "BillsByQuarter" having Year, Quarter, and Bill Amount as column names.
SET NOCOUNT ON
CREATE TABLE dbo.BillsByQuarter
(
theYear INT,
theQuarter INT,
theBillAmount INT,
PRIMARY KEY (Y,Q)
)
GO
Run the below query to add few records in the table "BillsByQuarter"
INSERT dbo.BillsByQuarter(theYear, theQuarter, theBillAmount)
SELECT 2007, 2, 79000
UNION SELECT 2007, 3, 21000
UNION SELECT 2007, 4, 24000
UNION SELECT 2008, 1, 12000
UNION SELECT 2008, 2, 24000
UNION SELECT 2008, 3, 42000
UNION SELECT 2008, 4, 87000
UNION SELECT 2009, 1, 34000
GO
The query below would sum the Bill Amount for each Quarter for all years
SELECT theYear,
Q1 = SUM(CASE WHEN theQuarter =1 THEN theBillAmount END),
Q2 = SUM(CASE WHEN theQuarter =2 THEN theBillAmount END),
Q3 = SUM(CASE WHEN theQuarter = 3 THEN theBillAmount END),
Q4 = SUM(CASE WHEN theQuarter = 4 THEN theBillAmount END)
FROM
dbo.BillsByQuarter
GROUP BY theYear
ORDER BY theYear
GO
Same results can now be achieved using PIVOT clause.
SELECT theYear,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT theYear, theQuarter, theBillAmount
FROM dbo.BillsByQuarter) s
PIVOT
(
SUM(theBillAmount)
FOR theQuarter IN ([1],[2],[3],[4])
) p
ORDER BY [theYear]
GO