datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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