太阳能的应用SQLServer 实现列转⾏
今天介绍4种列转⾏（⼜称宽表转长表、逆透视等）写法。
数据准备
SQL 写法
UNION
最传统最通⽤写法
小芳歌词
UNPIVOT
unpivot 的独特语法：
CROSS JOIN CREATE TABLE Sales ([Year ] INT ,Q1 INT ,Q2 INT ,Q3 INT ,Q4 INT );
彩铅画人物GO
INSERT INTO Sales ([Year ],Q1,Q2,Q3,Q4)常吃土豆有7大好处
VALUES (2017,12,123,324,123)
,(2018,13,455,324,878)
,(2019,29,786,473,633);
SELECT [Year ],'Q1' AS [Quarter ],Q1 AS Amount FROM Sales UNION ALL
SELECT [Year ],'Q2' AS [Quarter ],Q2 AS Amount FROM Sales UNION ALL
SELECT [Year ],'Q3' AS [Quarter ],Q3 AS Amount FROM Sales UNION ALL
SELECT [Year ],'Q4' AS [Quarter ],Q4 AS Amount FROM Sales ;
SELECT [Year ],[Quarter ],[Amount ]
似曾相识燕归来的上一句FROM Sales AS a
UNPIVOT (Amount FOR [Quarter ] IN (Q1,Q2,Q3,Q4)) AS b ;
其实就是笛卡尔积，⽤CROSS JOIN是MSSQL的推荐写法，其它数据库若不⽀持此语法，可以将CROSS JOIN替换为逗号，或⽤Join On 1 = 1
SELECT a.[Year],b.[Quarter]
,CASE b.[Quarter]
WHEN'Q1'THEN Q1
WHEN'Q2'THEN Q2
WHEN'Q3'THEN Q3
WHEN'Q4'THEN Q4
昆虫标本制作方法END AS Amout
FROM Sales a
CROSS JOIN(VALUES('Q1'),('Q2'),('Q3'),('Q4'))AS b([Quarter]);
CROSS APPLY
CROSS APPLY是个强⼤的操作符，也可以⽤来实现unpivot：
SELECT a.[Year], b.[Quarter],b.Amount
FROM Sales a
CROSS APPLY(SELECT'Q1'AS[Quarter],a.Q1 AS Amount UNION ALL
SELECT'Q2',a.Q2 UNION ALL
SELECT'Q3',a.Q3 UNION ALL
SELECT'Q4',a.Q4)AS b;
-
- 或
SELECT a.[Year], b.[Quarter],b.Amount
FROM Sales a
CROSS APPLY(VALUES('Q1',a.Q1)
小鹿男
茶叶保质期一般多久,('Q2',a.Q2)
,('Q3',a.Q3)
,('Q4',a.Q4))AS b([Quarter],Amount);
总结与说明
传统的Union写法需要扫描多次基表，效率最差
UNPIVOT写法最简洁，⽽且执⾏效率⾼，但语法有点晦涩，不是SQL的典型风格
CROSS JOIN与CROSS APPLY写法也只要扫描⼀次基表，可能执⾏效率⽐UNPIVOT差⼀点点，但好处是语法上更灵活
