查看: 195|回复: 0

[SQLServer] sql-pivot

发表于 6 天前

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

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

  1. 1 Select * From
  2. 2 (
  3. 3 Select empid,YEAR(orderdate) As OrderYear,val From Sales.OrderValues
  4. 4 ) 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)将列表中的值扩展到它们相应的目标列上。在逻辑上,相当于为IN子句中指定的每个目标列使用一下CASE表达式:

  CASE WHEN = THEN 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. 1 Select empid,
  2. 2 SUM(CASE WHEN orderyear=2006 THEN val END) AS [2006],
  3. 3 SUM(CASE WHEN orderyear=2007 THEN val END) AS [2007],
  4. 4 SUM(CASE WHEN orderyear=2008 THEN val END) AS [2008]
  5. 5 FROM (
  6. 6 SELECT empid,YEAR(orderdate) AS orderyear,val FROM Sales.OrderValues
  7. 7 ) AS OV
  8. 8 GROUP BY empid
复制代码

  



回复

使用道具 举报