DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

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/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE