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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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