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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to prepare monthly pivot report with CASE?

Execute the following SQL Server T-SQL example script in SSMS Query Editor to create a year (over rows) and monthly orders (across columns) crosstab (PIVOT) report on the sums of SubTotals for each sale:

USE AdventureWorks;

-- SQL case function - SQL crosstab query with case

-- SQL case expression - SQL pivot with case - transpose rows into columns

SELECT   YEAR = YEAR(OrderDate),

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 1 THEN SubTotal

             END), 1), '') AS 'JAN',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 2 THEN SubTotal

             END), 1), '') AS 'FEB',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 3 THEN SubTotal

             END), 1), '') AS 'MAR',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 4 THEN SubTotal

             END), 1), '') AS 'APR',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 5 THEN SubTotal

             END), 1), '') AS 'MAY',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 6 THEN SubTotal

             END), 1), '') AS 'JUN',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 7 THEN SubTotal

             END), 1), '') AS 'JUL',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 8 THEN SubTotal

             END), 1), '') AS 'AUG',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 9 THEN SubTotal

             END), 1), '') AS 'SEP',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 10 THEN SubTotal

             END), 1), '') AS 'OCT',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 11 THEN SubTotal

             END), 1), '') AS 'NOV',

         COALESCE(CONVERT(VARCHAR,SUM(CASE

               WHEN MONTH(OrderDate) = 12 THEN SubTotal

             END), 1), '') AS 'DEC'

FROM     Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

GO

/* Partial results

 

YEAR  JAN               FEB               MAR               APR

2002  1,453,196.54      2,833,324.01      2,391,928.57      1,724,736.56

2003  2,021,334.93      3,353,516.28      2,363,458.14      2,752,819.39

2004  3,340,283.42      4,712,382.32      4,771,752.73      4,274,109.22

*/

 

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