Microsoft SQL Server 2005 Best Practices

How to prepare monthly pivot report with CASE?

 

Execute the following script in Query Editor to create a year (rows) and monthly orders (columns) crosstab (PIVOT) report on the sums of SubTotals for each sale:

USE AdventureWorks;

SELECT Year=YEAR(OrderDate),
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JAN'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEB'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MAR'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APR'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAY'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUN'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JUL'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUG'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEP'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCT'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOV'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DEC'
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)



 

The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page