SQL之透视、逆透视及分组集

透视

假如当前有数据源如下所示：

有一报表需求如下所示：

这一类的需求就称之为数据透视转换。透视转换一般涉及分组、扩展及聚合三个阶段。上面的需求是按照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.Orders
7 GROUP BY empid;

下面是使用T-SQL PIVOT运算符进行透视转换：

1 SELECT empid, A, B, C, D
2 FROM (SELECT empid, custid, qty
3       FROM dbo.Orders) AS D
4   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.EmpCustOrders
10         CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
11 WHERE qty IS NOT NULL;

使用T-SQL的UNPIVOT运算符进行逆透视转换：

1 SELECT empid, custid, qty
2 FROM dbo.EmpCustOrders
3   UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;

 

转载于:https://www.cnblogs.com/laixiancai/p/4352614.html