The following
PIVOT-UNPIVOT operator T-SQL scripts demonstrate the usage of the operators on a simple dataset to generate crosstab(matrix) result:
-- EXAMPLE - PIVOT AW8 quarterly sales
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 #PivotXtab
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 #PivotXtab
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 #PivotXtab) 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 #PivotXtab
GO
------------
Related article:
http://www.sqlusa.com/bestpractices2005/dynamicpivot/
|
