|
 Crosstab (Matrix) Report Creation Using CASE
By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP
July 2, 2005
Crosstab reports involve listing of one category (dimension) horizontally
and a second category (dimension) vertically. The cells will contain a value, such as SUM aggregate,
corresponding to the cross section of the two categories. Since
SQL is vertically oriented, it is not easy to prepare a crosstab
report. The following example shows hard-wiring with CASE to achieve the
desired result. To make is dynamic requires dynamic SQL.
Here is an example:
-- T-SQL pivot rows into columns using the CASE function
USE pubs;
SELECT Store = left(st.stor_name,20),
YEAR = year(sa.ord_date),
Jan = SUM(CASE month(sa.ord_date)
WHEN 1 THEN sa.qty
ELSE 0
END),
Feb = SUM(CASE month(sa.ord_date)
WHEN 2 THEN sa.qty
ELSE 0
END),
Mar = SUM(CASE month(sa.ord_date)
WHEN 3 THEN sa.qty
ELSE 0
END),
Apr = SUM(CASE month(sa.ord_date)
WHEN 4 THEN sa.qty
ELSE 0
END),
May = SUM(CASE month(sa.ord_date)
WHEN 5 THEN sa.qty
ELSE 0
END),
Jun = SUM(CASE month(sa.ord_date)
WHEN 6 THEN sa.qty
ELSE 0
END),
Jul = SUM(CASE month(sa.ord_date)
WHEN 7 THEN sa.qty
ELSE 0
END),
Aug = SUM(CASE month(sa.ord_date)
WHEN 8 THEN sa.qty
ELSE 0
END),
Sep = SUM(CASE month(sa.ord_date)
WHEN 9 THEN sa.qty
ELSE 0
END),
Oct = SUM(CASE month(sa.ord_date)
WHEN 10 THEN sa.qty
ELSE 0
END),
Nov = SUM(CASE month(sa.ord_date)
WHEN 11 THEN sa.qty
ELSE 0
END),
[Dec] = SUM(CASE month(sa.ord_date)
WHEN 12 THEN sa.qty
ELSE 0
END)
FROM sales sa
JOIN stores st
ON sa.stor_id = st.stor_id
GROUP BY left(st.stor_name,20),
year(sa.ord_date)
ORDER BY year(sa.ord_date),
left(st.stor_name,20)
GO
This is the result set returned:
| Store |
YEAR |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
| News & Brews |
1992 |
0 |
0 |
0 |
0 |
0 |
80 |
0 |
0 |
0 |
0 |
0 |
0 |
| Barnum's |
1993 |
0 |
0 |
0 |
0 |
50 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
| Bookbeat |
1993 |
0 |
0 |
25 |
0 |
30 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
| Doc-U-Mat: Quality L |
1993 |
0 |
0 |
0 |
0 |
85 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
| Fricative Bookshop |
1993 |
0 |
35 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
15 |
0 |
10 |
| Barnum's |
1994 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
75 |
0 |
0 |
0 |
| Bookbeat |
1994 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
25 |
0 |
0 |
0 |
| Doc-U-Mat: Quality L |
1994 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
45 |
0 |
0 |
0 |
| Eric the Read Books |
1994 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
8 |
0 |
0 |
0 |
| News & Brews |
1994 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
10 |
0 |
0 |
0 |
Related article:
http://sqlusa.com/bestpractices2005/dynamicpivot/
|
