The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Pivoting your data in MS SQL. Creating "Cross-Tab" reports.

Feb8

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

A pivot is a normally used to transform large amounts data into a condensed list or grid. A Pivot report displays data in a grid along a vertical and horizontal axis. Financial reports are good candidates for Pivot reports.

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 SUMor 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.

Tags:
Categories:
blog comments powered by Disqus

1 comment(s) so far...


Gravatar

Re: Pivoting your data in MS SQL. Creating "Cross-Tab" reports.

Thanks Robert, that's pretty cool.

By John Creed on  2009/03/01 11:03 PM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<November 2017>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print