SQL 行列转换

前段时间有人问了一个问题，就是SQL的行列转换，当时有点懵没答上来，后来细细想一想，其实最近的一个项目就已经用到了。
基础数据如下：

处理前

要求根据当年的月份去统计出每一个ID的汇总金额。就是把行中的月切换到列中。
最后效果如下：

处理后
01,02...代表月份，02P,03P代表增长率。
整个过程用存储过程完成，比较复杂。下面记录一下SQL Server的函数直接转换。

行列转换

参考资料： https://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx#%E5%A4%8D%E6%9D%82%20PIVOT%20%E7%A4%BA%E4%BE%8B

带批注的 PIVOT 语法。

    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

如果聚合函数与 PIVOT 一起使用，则计算聚合时将不考虑出现在值列中的任何空值。

1 列转换为行数据

新建两张表

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);

查询结果

原始数据

--UNPIVOT 函数

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;

--单纯用SQL 处理 ,原理很简单，就是对每个需要置换的列单独处理出来

SELECT VendorID,'Emp1' Employee ,Emp1   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp2' Employee ,Emp2   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp3' Employee ,Emp3   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp4' Employee ,Emp4   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp5' Employee ,Emp5  Orders FROM dbo.pvt 

最终效果：

置换后

2 行列互换

--用上面的结果新建表

SELECT * INTO  PVT2 FROM 
SELECT VendorID,'Emp1' Employee ,Emp1   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp2' Employee ,Emp2   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp3' Employee ,Emp3   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp4' Employee ,Emp4   Orders FROM dbo.pvt 
UNION ALL 
SELECT VendorID,'Emp5' Employee ,Emp5  Orders FROM dbo.pvt 

--PIVOT

SELECT *   FROM  PVT2 AS  T1
PIVOT
(  sum (Orders)   FOR Employee IN (Emp1,Emp2,Emp3,Emp4,Emp5) )
AS  T

-- SQL直接处理，先用CASE 语句将每行处理出来，然后在用聚合去处理合并相同ID的行。

SELECT VendorID ,SUM(EMP1) EMP1,SUM(EMP2) EMP2,SUM(EMP3) EMP3,SUM(EMP4) EMP4,SUM(EMP5) EMP5
FROM (
SELECT VendorID,
CASE Employee WHEN 'EMP1' THEN  (Orders) ELSE 0 END EMP1 ,
CASE Employee WHEN 'EMP2' THEN  (Orders) ELSE 0 END EMP2,
CASE Employee WHEN 'EMP3' THEN  (Orders) ELSE 0 END EMP3 ,
CASE Employee WHEN 'EMP4' THEN  (Orders) ELSE 0 END EMP4,
CASE Employee WHEN 'EMP5' THEN  (Orders) ELSE 0 END EMP5
  FROM  PVT2
  )AS T
GROUP BY VendorID