sql-pivot

2017-09-04 20:11:37来源:cnblogs.com作者:zhyue93人点击

分享

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

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

1 Select * From 2 (3     Select empid,YEAR(orderdate) As OrderYear,val From Sales.OrderValues4 ) 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 查询逻辑上相当于以下查询:

1 Select empid,2 SUM(CASE WHEN orderyear=2006 THEN val END) AS [2006],3 SUM(CASE WHEN orderyear=2007 THEN val END) AS [2007],4 SUM(CASE WHEN orderyear=2008 THEN val END) AS [2008]5 FROM (6     SELECT empid,YEAR(orderdate) AS orderyear,val FROM Sales.OrderValues7 ) AS OV8 GROUP BY empid

  

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台