透视转换是把数据从行的状态旋转为列的状态,逆透视转换测试把数据从列的状态旋转为行的状态。数据库: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