|
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
*/
|
