sql-pivot

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.OrderValues
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）將<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.OrderValues
7 ) AS OV
8 GROUP BY empid