SQLUSA
SAVE UP TO 50% ON COMBOS
FREE TRIAL
CLICK HERE TO ORDER

SQL Server 2008 Best Practices
SQL Server 2005 Best Practices

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

*/

 

The World Leader in SQL Server 2008 Training
The future is just a CLICK away. Your future!
 
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.