T-SQL 透视&逆透视

透视转换是把数据从行的状态旋转为列的状态，逆透视转换测试把数据从列的状态旋转为行的状态。数据库：Northwind->Orders。

• 透视转换(三个阶段：分组，扩展，聚合)

1、标准SQL

SELECT 
	EmployeeID,
	COUNT(CASE WHEN YEAR(OrderDate)='1996' THEN OrderID END) AS [1996],
	COUNT(CASE WHEN YEAR(OrderDate)='1997' THEN OrderID END) AS [1997],
	COUNT(CASE WHEN YEAR(OrderDate)='1998' THEN OrderID END) AS [1998],
	COUNT(EmployeeID) AS Total
FROM Orders
GROUP BY EmployeeID
ORDER BY EmployeeID

2、T-SQL PIVOT

SELECT EmployeeID,[1996],[1997],[1998],[1996]+[1997]+[1998] AS Total
FROM (
	SELECT EmployeeID,YEAR(OrderDate) AS OrderYear,OrderID  FROM Orders
) AS t 
PIVOT (
	COUNT(OrderID) FOR OrderYear IN ([1996],[1997],[1998])
) AS p
ORDER BY EmployeeID

结果

EmployeeID  1996        1997        1998        Total
----------- ----------- ----------- ----------- -----------
1           26          55          42          123
2           16          41          39          96
3           18          71          38          127
4           31          81          44          156
5           11          18          13          42
6           15          33          19          67
7           11          36          25          72
8           19          54          31          104
9           5           19          19          43

• 逆透视转换(三个阶段：生成副本，提取元素，删除不相关的交叉)

1、标准SQL，为了结构更清晰，此处使用公用表表达式(CTE)

WITH T1 AS 
(
	SELECT 
		EmployeeID,
		COUNT(CASE WHEN YEAR(OrderDate)='1996' THEN OrderID END) AS [1996],
		COUNT(CASE WHEN YEAR(OrderDate)='1997' THEN OrderID END) AS [1997],
		COUNT(CASE WHEN YEAR(OrderDate)='1998' THEN OrderID END) AS [1998]
	FROM Orders
	GROUP BY EmployeeID
),
T2 AS
(
	SELECT 1996 AS OrderYear
	UNION ALL
	SELECT 1997
	UNION ALL
	SELECT 1998
)
SELECT * FROM  
(
	SELECT EmployeeID,OrderYear,
		CASE OrderYear
		WHEN 1996 THEN [1996]
		WHEN 1997 THEN [1997] 
		WHEN 1998 THEN [1998] 
		END AS Quantity
	 FROM T1
	CROSS JOIN T2 
) AS T3 
WHERE Quantity IS NOT NULL 
ORDER BY EmployeeID

2、T-SQL UNPIVOT

WITH T1 AS 
(
	SELECT 
		EmployeeID,
		COUNT(CASE WHEN YEAR(OrderDate)='1996' THEN OrderID END) AS [1996],
		COUNT(CASE WHEN YEAR(OrderDate)='1997' THEN OrderID END) AS [1997],
		COUNT(CASE WHEN YEAR(OrderDate)='1998' THEN OrderID END) AS [1998]
	FROM Orders
	GROUP BY EmployeeID
)
SELECT EmployeeID,OrderYear,Quantity FROM T1 
UNPIVOT (Quantity FOR OrderYear IN ([1996],[1997],[1998])) AS U
ORDER BY EmployeeID

结果

EmployeeID  OrderYear   Quantity
----------- ----------- -----------
1           1996        26
1           1997        55
1           1998        42
2           1996        16
2           1997        41
2           1998        39
3           1996        18
3           1997        71
3           1998        38
4           1996        31
4           1997        81
4           1998        44
5           1996        11
5           1997        18
5           1998        13
6           1996        15
6           1997        33
6           1998        19
7           1996        11
7           1997        36
7           1998        25
8           1996        19
8           1997        54
8           1998        31
9           1996        5
9           1997        19
9           1998        19