T-SQL查询： PIVOT/UNPIVOT 逻辑操作步骤

--【PIVOT】


USE tempdb
GO

--	DROP TABLE #test
CREATE TABLE #test(Empid int,Oderyear varchar(4),val numeric(18,2))

INSERT INTO #test(Empid,Oderyear,val)
VALUES
	(1,'2012',156823),
	(1,'2013',256833.56),
	(1,'2014',456823.33),
	(3,'2014',26536.69),
	(4,'2012',56823.85),
	(4,'2013',452365.23),
	(2,'2014',56213),
	(3,'2013',456123),
	(4,'2014',256423.33)
	


SET STATISTICS PROFILE ON --查看执行计划

SELECT * FROM  #test
PIVOT(
	MAX(VAL) FOR Oderyear IN([2012],[2013],[2014])
) AS T

/*
SELECT * FROM  #test  PIVOT(   MAX(VAL) FOR Oderyear IN([2012],[2013],[2014])  ) AS T
     |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[#test].[Empid]) DEFINE:(
		[Expr1004]=MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2012' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END)
		[Expr1005]=MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2013' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END)
		[Expr1006]=MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2014' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END))
       |--Sort(ORDER BY:([tempdb].[dbo].[#test].[Empid] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#test]))

1. 读取表[#test]
2. 表排序[#test].[Empid] ASC
3. 聚合查询(按查询的步骤执行)
	3.1 分组GROUP BY:([tempdb].[dbo].[#test].[Empid])
	3.2 扩展，将IN子句扩展到相应目标列
		,CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2012' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END
		,CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2013' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END
		,CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2014' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END
	3.3 聚合
		,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2012' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2012]
		,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2013' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2013]
		,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2014' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2014]
*/
  
--逻辑上等效如下：
SELECT [tempdb].[dbo].[#test].[Empid]
,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2012' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2012]
,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2013' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2013]
,MAX(CASE WHEN [tempdb].[dbo].[#test].[Oderyear]='2014' THEN [tempdb].[dbo].[#test].[val] ELSE NULL END) AS [2014]
FROM [tempdb].[dbo].[#test]
GROUP BY [tempdb].[dbo].[#test].[Empid]

/*结果：
Empid	2012	2013		2014
-----	-----	--------	--------
1	156823.00	256833.56	456823.33
2	NULL		NULL		56213.00
3	NULL		456123.00	26536.69
4	56823.85	452365.23	256423.33

*/

--【UNPIVOT】
--	DROP TABLE #test2

SELECT * INTO #test2 FROM  #test
PIVOT(
	MAX(VAL) FOR Oderyear IN([2012],[2013],[2014])
) AS T

--以刚才的结果为例

SELECT Empid,Oderyear,val FROM  #test2
UNPIVOT(
	VAL FOR Oderyear IN([2012],[2013],[2014])
) AS T

/*执行计划:
SELECT Empid,Oderyear,val FROM  #test2  UNPIVOT(   VAL FOR Oderyear IN([2012],[2013],[2014])  ) AS T
  |--Filter(WHERE:([Expr1008] IS NOT NULL))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:(
		[tempdb].[dbo].[#test2].[2012],[tempdb].[dbo].[#test2].[2013],[tempdb].[dbo].[#test2].[2014]))
            |--Compute Scalar(DEFINE:([Expr1004]=[tempdb].[dbo].[#test2].[Empid]))
            |    |--Table Scan(OBJECT:([tempdb].[dbo].[#test2]))
            |--Constant Scan(VALUES:(
            (N'2012',[tempdb].[dbo].[#test2].[2012]),(N'2013',[tempdb].[dbo].[#test2].[2013]),(N'2014',[tempdb].[dbo].[#test2].[2014])))
1. 读取表[#test2]
2. 嵌套连接[Empid]与
			(N'2012',[tempdb].[dbo].[#test2].[2012]),
			(N'2013',[tempdb].[dbo].[#test2].[2013]),
			(N'2014',[tempdb].[dbo].[#test2].[2014])
	生成3个副本,IN子句有几个列就生成几个副本
	结果:
	Empid	2012	2013		2014		Oderyear
	-----	-----	--------	--------	----
	1	156823.00	256833.56	456823.33	2012
	1	156823.00	256833.56	456823.33	2013
	1	156823.00	256833.56	456823.33	2014
	2	NULL		NULL		56213.00	2012
	2	NULL		NULL		56213.00	2013
	2	NULL		NULL		56213.00	2014
	3	NULL		456123.00	26536.69	2012
	3	NULL		456123.00	26536.69	2013
	3	NULL		456123.00	26536.69	2014
	4	56823.85	452365.23	256423.33	2012
	4	56823.85	452365.23	256423.33	2013
	4	56823.85	452365.23	256423.33	2014

3. 提取元素,VAL FOR Oderyear 中保留对应的值
	结果:
	Empid	VAL		Oderyear
	-----	-----	--------
	1	156823.00	2012
	1	256833.56	2013
	1	456823.33	2014
	2	NULL		2012
	2	NULL		2013
	2	56213.00	2014
	3	NULL		2012
	3	456123.00	2013
	3	26536.69	2014
	4	56823.85	2012
	4	452365.23	2013
	4	256423.33	2014

4. 过滤空值行Filter(WHERE:([Expr1008] IS NOT NULL)
	结果:
	Empid	VAL		Oderyear
	-----	-----	--------
	1	156823.00	2012
	1	256833.56	2013
	1	456823.33	2014
	2	56213.00	2014
	3	456123.00	2013
	3	26536.69	2014
	4	56823.85	2012
	4	452365.23	2013
	4	256423.33	2014

*/