sql-pivot

 

PIVOT
　　PIVOT运算符用于在列和行之间进行数据旋转或透视转换，同时执行聚合运算

Select * From Sales.OrderValues Where YEAR(orderdate) In(2006,2007,2008) Order By empid asc

Select * From 
(
    Select empid,YEAR(orderdate) As OrderYear,val From Sales.OrderValues
) OV Pivot(Sum(val) For OrderYear In([2006],[2007],[2008])) P

　　返回每个雇员每年处理过的订单的总价格。在输出中，每个雇员占一行，每个订单年份占一列，每个雇员行和订单年份列的交叉位置上则是总价格。

　　PIVOT运算符用一个名为OV的表表达式作为它的左输入：每个订单占一行，包含雇员ID（empid）、订单年份（orderyear）和订单价格（val）。PIVOT操作涉及以下三个步骤：

　　1、P1：分组

　　2、P2：扩展

　　3、P3：聚合

　　从查询语句中看到PIVOT运算符引用了OV中的两个列作为输入参数（val和orderyear）。第一阶段会隐式地对OV的行进行分组，分组依据是那些未作为PIVOT输入的所有列，就像是有一个隐藏的Group By子句。在这个示例中只有empid列没有出现在PIVOT的输入参数中。所以每个雇员都将有一个组。

注意：

　　PIVOT的隐式分组阶段并不会影响查询中任何显式的GROUP BY子句。PIVOT运算最终会生成一个虚拟表，作为下一个逻辑阶段（也许是另一个表运算，也许是WHERE阶段）的输入。当查询中同时出现PIVOT和GROUP BY时，会有两个单独的分组阶段：一个是PIVOT的第一个阶段（P1），另一个是查询的GROUP BY阶段。

　　PIVOT的第二个阶段（P2）将<spreading_col>列表中的值扩展到它们相应的目标列上。在逻辑上，相当于为IN子句中指定的每个目标列使用一下CASE表达式：

　　CASE WHEN <spereading_col> = <target_col_element> THEN <expression> END

　　在这个例子中相当于应用了下面3个表达式：

　　CASE WHEN orderyear = 2006 THEN val END,
　　CASE WHEN orderyear = 2007 THEN val END,
　　CASE WHEN orderyear = 2008 THEN val END

　　注意：不带ELSE子句的CASE表达式相当于包含一个隐式的 ELSE NULL

　　对于每个目标列，只有来源行具有相应的订单年份时，CASE 表达式才会返回订单价格（val列），否则，CASE 表达式将返回 NULL

PIVOT 的第三个阶段（P3）对每个 CASE 表达式应用指定的聚合函数，该例中逻辑上等效的表达式应该是这样：

　　SUM(CASE WHEN orderyear = 2006 THEN val END) AS [2006],
　　SUM(CASE WHEN orderyear = 2007 THEN val END) AS [2007],
　　SUM(CASE WHEN orderyear = 2008 THEN val END) AS [2008]

　　前面的 PIVOT 查询逻辑上相当于以下查询：

Select empid,
SUM(CASE WHEN orderyear=2006 THEN val END) AS [2006],
SUM(CASE WHEN orderyear=2007 THEN val END) AS [2007],
SUM(CASE WHEN orderyear=2008 THEN val END) AS [2008]
FROM (
    SELECT empid,YEAR(orderdate) AS orderyear,val FROM Sales.OrderValues
) AS OV
GROUP BY empid