Feb 8

Written by:
2009/02/08 09:40 PM 

The picture below displays a pivot type report, displaying sales data in a x:y grid. The Year of the sales running along one axis and the Quarter the sales were made running along the other. The sales amount is summed for each quarter and year. Typically you would also have x:y totals on the report. Each Quater would have a total at the bottom, and each Year would have a total on the right.

YearSold	Q1	Q2	Q3	Q4
2003	1.00	2.00	3.00	4.00
2004	2.00	6.00	7.00	8.00
2005	9.00	10.00	0.00	0.00

 

Microsoft Excel users have used Pivot reports as part of their standard day to day operation. In Microsoft Excel, there is a drag and drop wizard that walks a user though all the steps needed to create a pivot table. Excel Pivot Tables can have their Column and Rows dynamically changed by dragging the desired column to the correct axis.

Microsoft Access refers to pivots as "Cross tabs." In MS Acces there is a wizard as well.  It asks which columns should be on which axis, then auto-generates the code needed. Access creates pivots by using the special key word TRANSFORM:

TRANSFORM Sum(SALES.AMOUNT) AS SumOfAMOUNT

SELECT SALES.YEAR, Sum(SALES.AMOUNT) AS [Total Of AMOUNT]

FROM SALES

GROUP BY SALES.YEAR

PIVOT SALES.QUARTER;

Unfortunately it is not as easy as using a wizard in MS SQL. In SQL 2000, pivot style reports, called "Cross-Tab Reports" can be created using the CASE keyword. CASE can also be used in SQL 2005 and SQL 2008. But there is another keyword that can be used, PIVOT.

The CASE Keyword

The keyword CASE can be used to build pivots in both SQL 2000 and SQL 2005. A Pivot, is simply a grid with an X Y axis and populated with summarized data. The main purpose of CASE in T-SQL is to allow another value to be displayed during a Select statement. This following example creates a small table holding province abbreviations. CASE will be used to display the Province name.

CREATE TABLE #Province

(

ProvinceCode char(3)

)

INSERT INTO #Province VALUES ('GA')

INSERT INTO #Province VALUES ('KZN')

INSERT INTO #Province VALUES ('WP')

INSERT INTO #Province VALUES ('FS')

SELECT ProvinceCode,

 CASE ProvinceCode

 WHEN 'GA' THEN 'Gauteng'

 WHEN 'KZN' THEN 'Kwazulu Natal'

 WHEN 'WP' THEN 'Western Province'

 WHEN 'FS' THEN 'Free State'

 END AS Provonce_Name

FROM #Province

DROP TABLE #Province

Which would product the following.

 

ProvinceCode	Province_Name
GA	Gauteng
KZN	Kwazulu Natal
WP	Western Province
FS	Free State

The CASE syntax is really not that complicate. WHEN a condition is true, CASE will display the alternative value provided after the THEN.

Case and Pivot Reports

So how can I use Case to create a pivot style report in both SQL 2000 and SQL 2005? Lets create an example sales report.  The year of the sale will run along the vertical axis, and the quarter for that year along the horizontal axis.

CREATE TABLE #sales

(

YearSold int,

Quarter char(2),

Amount money

)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2003, 'Q1', 1)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2003, 'Q2', 2)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2003, 'Q3', 3)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2003, 'Q4', 4)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2004, 'Q1', 5)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2004, 'Q2', 6)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2004, 'Q3', 7)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2004, 'Q4', 8)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2005, 'Q1', 9)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2005, 'Q2', 10)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2005, 'Q3', 0)

INSERT INTO #sales (YearSold, Quarter, Amount) values (2005, 'Q4', 0)


SELECT YearSold,

 SUM(CASE Quarter WHEN 'Q1' THEN Amount ELSE 0 END) AS Q1,

 SUM(CASE Quarter WHEN 'Q2' THEN Amount ELSE 0 END) AS Q2,

 SUM(CASE Quarter WHEN 'Q3' THEN Amount ELSE 0 END) AS Q3,

 SUM(CASE Quarter WHEN 'Q4' THEN Amount ELSE 0 END) AS Q4

FROM #sales

GROUP BY YearSold

The statement produces the grid as expected, with Year and Quarter as an axis, and Amount summed. The GROUP BY command was included so only quarters for each unique year would be summed for that given year. The keyword ELSE was included so that nulls would not be summed.

YearSold	Q1	Q2	Q3	Q4
2003	1.00	2.00	3.00	4.00
2004	2.00	6.00	7.00	8.00
2005	9.00	10.00	0.00	0.00

 

The Pivot Keyword

The PIVOT keyword is new in SQL 2005. It was designed, you guessed it, for pivots. Its syntax is more readable than a bunch of CASE statements. Especially if you have a few of those case statements. This next statement produces a report identical to the proceeding CASE Pivot report. In the code below, SUM specifies the grid data and FOR dictates the horizontal axis, IN creates a filter for SUM to match.

SELECT *

FROM #sales

PIVOT

 (

 SUM(Amount)

 FOR Quarter

 IN (Q1, Q2, Q3, Q4)

 )

 AS p

In this statement, the letter "p" is used as a alias for the derived table the Picot command creates. The PIVOT command automatically groups all columns not listed in SUM or FOR.

Keep in mind that if you have lots of columns your Pivot might not turn out the way you expected. You would have to qualify the columns to be used in the Pivot with a sub select statement. Remember this rule, Two axis to pivot and a data column to sum. The first being the y-axis, the second being the x-axis, and the third is the data to be summed. Have a look at the code below.

SELECT *

FROM (SELECT YearSold, Quarter, Amount FROM #sales) as s

PIVOT

 (

 SUM(Amount)

 FOR Quarter

 IN (Q1, Q2, Q3, Q4)

 )

 AS p

Pivots can be used to present large amounts of data in a readable report format. They can be used to summarise data in a format that is quick and easy to read and follow.

blog comments powered by Disqus