UNPIVOT的详细说明

 

unpivot包含3步：1.生成拷贝 2.提取数据 3.过滤不相关的数据

第一步，把每行数据都打散开来，用CROSS JOIN实现

{(1,A),(1,B),(1,C),(1,D)}

{(2,A),(2,B),(2,C),(2,D)}

....................

2008语法

SELECT *
FROM dbo.EmpCustOrders
  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

原本的语法

SELECT *
FROM dbo.EmpCustOrders
  CROSS JOIN (SELECT 'A' AS custid
              UNION ALL SELECT 'B'
              UNION ALL SELECT 'C'
              UNION ALL SELECT 'D') AS Custs;

empid       A           B           C           D
----------- ----------- ----------- ----------- -----------
1           NULL        20          34          NULL
2           52          27          NULL        NULL
3           20          NULL        22          30

empid       A           B           C           D           custid
----------- ----------- ----------- ----------- ----------- ------
1           NULL        20          34          NULL        A
1           NULL        20          34          NULL        B
1           NULL        20          34          NULL        C
1           NULL        20          34          NULL        D
2           52          27          NULL        NULL        A
2           52          27          NULL        NULL        B
2           52          27          NULL        NULL        C
2           52          27          NULL        NULL        D
3           20          NULL        22          30          A
3           20          NULL        22          30          B
3           20          NULL        22          30          C
3           20          NULL        22          30          D

第二步：提取数据

第一步中的表只是为了方便展示第二部的CASE用的

SELECT empid, custid,
  CASE custid
    WHEN 'A' THEN A
    WHEN 'B' THEN B
    WHEN 'C' THEN C
    WHEN 'D' THEN D
  END AS qty
FROM dbo.EmpCustOrders
  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

empid       custid    qty
----------- --------- -----------
1           A         NULL
1           B         20
1           C         34
1           D         NULL
2           A         52
2           B         27
2           C         NULL
2           D         NULL
3           A         20
3           B         NULL
3           C         22
3           D         30

第三步，用IS NOT NULL来清除NULL行

SELECT *
FROM (SELECT empid, custid,
        CASE custid
          WHEN 'A' THEN A
          WHEN 'B' THEN B
          WHEN 'C' THEN C
          WHEN 'D' THEN D
        END AS qty
      FROM dbo.EmpCustOrders
        CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
WHERE qty IS NOT NULL;

注意PIVOT 和UNPIVOT不是逆反的过程，因为已经聚合过了。

比如说(1   C  34)这行，其中的34就是由元数据表中的

1, 'C', 20

1, 'C', 14

聚合而成。