|
Execute the following
SQL Server T-SQL example script in SSMS Query Editor to create a cross tabulation with the CASE function and the PIVOT operator, and how to UNPIVOT columns to rows.
The year of TransactionDate is swapped across into columns. The GROUP BY aggregate function SUM calculates the total cost.
USE AdventureWorks
GO
-- SQL pivot for crosstab report - SQL pivot rows into columns - SQL case function
-- SQL swap rows to columns - SQL sum group by order by
SELECT ProductName = p.Name,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2000 THEN ActualCost
END),1) AS Y2000,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2001 THEN ActualCost
END),1) AS Y2001,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2002 THEN ActualCost
END),1) AS Y2002,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2003 THEN ActualCost
END),1) AS Y2003,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2004 THEN ActualCost
END),1) AS Y2004,
'$' + CONVERT(VARCHAR,SUM(CASE
WHEN Year(TransactionDate) = 2005 THEN ActualCost
END),1) AS Y2005
FROM Production.TransactionHistory th
JOIN Production.Product p
ON p.ProductID = th.ProductID
GROUP BY p.Name
ORDER BY p.Name
GO
/* Partial results
ProductName Y2003 Y2004
ML Road Frame-W - Yellow, 38 $25,089.93 $32,092.70
ML Road Frame-W - Yellow, 40 $6,162.44 $0.00
ML Road Frame-W - Yellow, 42 $0.00 $0.00
ML Road Frame-W - Yellow, 44 $38,215.12 $35,574.08
ML Road Frame-W - Yellow, 48 $22,849.04 $32,052.68
*/
USE AdventureWorks2008;
/****************** PIVOT OPERATION *******************/
WITH cteQuery
AS (SELECT YEAR = YEAR(orderDate),
QUARTER = DatePart(qq,OrderDate),
Sales = Sum(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(orderDate), DatePart(qq,OrderDate))
/* "Vertical" Sales data in many rows and 1 column from CTE
YEAR QUARTER Sales
2001 3 5850932.9483
2002 4 10827327.4904
2003 2 10749269.374
2002 1 7379686.3091
2002 2 8210285.1655
2004 1 14170982.5455
2003 3 18220131.5285
2003 1 8550831.8702
2004 2 17969750.9487
2001 4 8476619.278
2003 4 16787382.3141
2002 3 13458206.13
2004 3 56178.9223
*/
SELECT YEAR, Q1 = [1], Q2 = [2], Q3 = [3], Q4 = [4]
INTO #PivotTable
FROM (SELECT * FROM cteQUERY) AS PivotInput
PIVOT
(SUM(Sales) -- Aggregate for cells
FOR QUARTER IN ( [1],[2],[3],[4] ) ) AS PivotOutput
ORDER BY YEAR;
SELECT * FROM #PivotTable
GO
/* Pivotted sales data
YEAR Q1 Q2 Q3 Q4
2001 NULL NULL 5850932.94 8476619.278
2002 7379686.3091 8210285.165 13458206.13 10827327.4904
2003 8550831.8702 10749269.37 18220131.52 16787382.3141
2004 14170982.5455 17969750.94 56178.9223 NULL
*/
/****************** UNPIVOT OPERATION *******************/
SELECT YEAR,
Quarter,
Sales
FROM (SELECT *
FROM #PivotTable) p
UNPIVOT
(Sales
FOR Quarter IN ( [Q1],[Q2],[Q3],[Q4] ) ) AS unpvt
ORDER BY YEAR,
Quarter;
GO
/* Sales data in many rows again and in 1 column
Year Quarter Sales
2001 Q3 5850932.9483
2001 Q4 8476619.278
2002 Q1 7379686.3091
2002 Q2 8210285.1655
2002 Q3 13458206.13
2002 Q4 10827327.4904
2003 Q1 8550831.8702
2003 Q2 10749269.374
2003 Q3 18220131.5285
2003 Q4 16787382.3141
2004 Q1 14170982.5455
2004 Q2 17969750.9487
2004 Q3 56178.9223
*/
DROP TABLE #PivotTable
GO
------------ |
