透视
假如当前有数据源如下所示:
有一报表需求如下所示:
这一类的需求就称之为数据透视转换。透视转换一般涉及分组、扩展及聚合三个阶段。上面的需求是按照empid进行分组,按照custid对订货量进行扩展,最后进行聚合SUM(qty)。数据透视转换其实是存在某种通用查询模板的。下面是上面这个需求的标准SQL解决方案:
1 SELECT empid,2 SUM(CASE WHEN custid = 'A' THEN qty END) AS A,3 SUM(CASE WHEN custid = 'B' THEN qty END) AS B,4 SUM(CASE WHEN custid = 'C' THEN qty END) AS C,5 SUM(CASE WHEN custid = 'D' THEN qty END) AS D 6 FROM dbo.Orders7 GROUP BY empid;
下面是使用T-SQL PIVOT运算符进行透视转换:
1 SELECT empid, A, B, C, D2 FROM (SELECT empid, custid, qty3 FROM dbo.Orders) AS D4 PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
注意,一般不直接把PIVOT运算符应用到源数据表(本例为Orders表),而是将其应用到一个表表达式(该表表达式只包含透视转换需要的3种元素,不包含其他属性)。
逆透视
标准SQL的解决方案:
1 SELECT * 2 FROM (SELECT empid, custid, 3 CASE custid 4 WHEN 'A' THEN A 5 WHEN 'B' THEN B 6 WHEN 'C' THEN C 7 WHEN 'D' THEN D 8 END AS qty 9 FROM dbo.EmpCustOrders10 CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D11 WHERE qty IS NOT NULL;
使用T-SQL的UNPIVOT运算符进行逆透视转换:
1 SELECT empid, custid, qty2 FROM dbo.EmpCustOrders3 UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;