http://msdn.microsoft.com/zh-cn/library/ms177410%28v=SQL.90%29.aspx

可以使用 PIVOT 和 UNPIVOT 關(guān)系運(yùn)算符將表值表達(dá)式更改為另一個表。PIVOT 通過將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個列來旋轉(zhuǎn)表值表達(dá)式，并在必要時對最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT 與 PIVOT 執(zhí)行相反的操作，將表值表達(dá)式的列轉(zhuǎn)換為列值。

注意：

對升級到 Microsoft SQL Server 2005 的數(shù)據(jù)庫使用 PIVOT 和 UNPIVOT 時，數(shù)據(jù)庫的兼容級別必須設(shè)置為 90。有關(guān)如何設(shè)置數(shù)據(jù)庫兼容級別的信息，請參閱 sp_dbcmptlevel (Transact-SQL) 。

PIVOT 提供的語法比一系列復(fù)雜的 SELECT...CASE 語句中所指定的語法更簡單和更具可讀性。有關(guān) PIVOT 語法的完整說明，請參閱 FROM (Transact-SQL) 。

以下是帶批注的 PIVOT 語法。

SELECT < non-pivoted column > ,

??? [ first pivoted column ] AS < column name > ,

??? [ second pivoted column ] AS < column name > ,

...

??? [ last pivoted column ] AS < column name >

FROM

( < SELECT query that produces the data > )

AS < alias for the source query >

PIVOT

(

<aggregation function>( < column being aggregated > )

FOR

[< column that contains the values that will become column headers >]

IN ( [ first pivoted column ] , [ second pivoted column ] ,

... [ last pivoted column ] )

) AS < alias for the pivot table >

< optional ORDER BY clause >

SELECT <non-pivoted column>,

??? [first pivoted column] AS <column name>,

??? [second pivoted column] AS <column name>,

??? ...

??? [last pivoted column] AS <column name>

FROM

??? (<SELECT query that produces the data>)

?? AS <alias for the source query>

PIVOT

(

??? <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

??? IN ( [first pivoted column], [second pivoted column],

??? ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>

簡單 PIVOT 示例

下面的代碼示例生成一個兩列四行的表。

    USE AdventureWorks ;

GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 

FROM Production.Product

GROUP BY DaysToManufacture




  

下面是結(jié)果集：

DaysToManufacture????????? AverageCost

0????????????????????????? 5.0885

1????????????????????????? 223.88

2????????????????????????? 359.1082

4????????????????????????? 949.4105

沒有定義 DaysToManufacture 為 3 的產(chǎn)品。

以下代碼顯示相同的結(jié)果，該結(jié)果經(jīng)過透視以使 DaysToManufacture 值成為列標(biāo)題。提供一個列表示三 [3] 天，即使結(jié)果為 NULL 。

    -- Pivot table with one row and five columns

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 

[0], [1], [2], [3], [4]

FROM

(SELECT DaysToManufacture, StandardCost???? FROM Production.Product) AS SourceTable

PIVOT

(

AVG(StandardCost)

FOR DaysToManufacture IN ([0], [1], [2], [3], [4])

) AS PivotTable




  

下面是結(jié)果集：

Cost_Sorted_By_Production_Days??? 0???????? 1???????? 2?????????? 3?????? 4??????

AverageCost?????????????????????? 5.0885??? 223.88??? 359.1082??? NULL??? 949.4105

復(fù)雜 PIVOT 示例

可能會用到 PIVOT 的常見情況是：需要生成交叉表格報表以匯總數(shù)據(jù)。例如，假設(shè)需要在 AdventureWorks 示例數(shù)據(jù)庫中查詢 PurchaseOrderHeader 表以確定由某些特定雇員所下的采購訂單數(shù)。以下查詢提供了此報表（按供應(yīng)商排序）。

    USE AdventureWorks;

GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM 

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID


  

以下為部分結(jié)果集。

    VendorID    Emp1        Emp2        Emp3        Emp4        Emp5

1           4           3           5           4           4

2           4           1           5           5           5

3           4           3           5           4           4

4           4           2           5           5           4

5           5           1           5           5           5


  

將在 EmployeeID 列上透視此嵌套 select 語句返回的結(jié)果。

    SELECT PurchaseOrderID, EmployeeID, VendorID

FROM PurchaseOrderHeader


  

這意味著 EmployeeID 列返回的唯一值自行變成了最終結(jié)果集中的字段。結(jié)果，在透視子句中指定的每個 EmployeeID 號都有相應(yīng)的一列：在本例中為雇員 164 、 198 、 223 、 231 和 233 。 PurchaseOrderID 列作為值列，將根據(jù)此列對最終輸出中返回的列（稱為分組列）進(jìn)行分組。在本例中，通過 COUNT 函數(shù)聚合分組列。請注意，將顯示一條警告消息，指出為每個雇員計(jì)算 COUNT 時未考慮顯示在 PurchaseOrderID 列中的任何空值。

重要提示：

如果聚合函數(shù)與 PIVOT 一起使用，則計(jì)算聚合時將不考慮出現(xiàn)在值列中的任何空值。

UNPIVOT 將與 PIVOT 執(zhí)行幾乎完全相反的操作，將列轉(zhuǎn)換為行。假設(shè)以上示例中生成的表在數(shù)據(jù)庫中存儲為 pvt ，并且您需要將列標(biāo)識符 Emp1 、 Emp2 、 Emp3 、 Emp4 和 Emp5 旋轉(zhuǎn)為對應(yīng)于特定供應(yīng)商的行值。這意味著必須標(biāo)識另外兩個列。包含要旋轉(zhuǎn)的列值（ Emp1 、 Emp2 ...）的列將被稱為 Employee ，將保存當(dāng)前位于待旋轉(zhuǎn)列下的值的列被稱為 Orders 。這些列分別對應(yīng)于 Transact-SQL 定義中的 pivot_column 和 value_column 。以下為該查詢。

    --Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

Emp3 int, Emp4 int, Emp5 int)

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4)

INSERT INTO pvt VALUES (2,4,1,5,5,5)

INSERT INTO pvt VALUES (3,4,3,5,4,4)

INSERT INTO pvt VALUES (4,4,2,5,5,4)

INSERT INTO pvt VALUES (5,5,1,5,5,5)

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM 

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM pvt) p

UNPIVOT

   (Orders FOR Employee IN 

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

GO


  

以下為部分結(jié)果集。

    VendorID   Employee   Orders

1      Emp1         4

1      Emp2         3

1      Emp3         5

1      Emp4         4

1      Emp5         4

2      Emp1         4

2      Emp2         1

2      Emp3         5

2      Emp4         5

2      Emp5         5

...


  

請注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 會執(zhí)行一次聚合，從而將多個可能的行合并為輸出中的單個行。而 UNPIVOT 不會重現(xiàn)原始表值表達(dá)式的結(jié)果，因?yàn)樾幸呀?jīng)被合并了。另外，UNPIVOT 的輸入中的空值不會顯示在輸出中，而在執(zhí)行 PIVOT 操作之前，輸入中可能有原始的空值。

AdventureWorks 示例數(shù)據(jù)庫中的 Sales.vSalesPersonSalesByFiscalYears 視圖將使用 PIVOT 返回每個銷售人員在每個會計(jì)年度的總銷售額。若要在 SQL Server Management Studio 中編寫視圖腳本，請?jiān)? “對象資源管理器” 中，在 “視圖” 文件夾下找到 AdventureWorks 數(shù)據(jù)庫對應(yīng)的視圖。右鍵單擊該視圖名稱，再選擇 “編寫視圖腳本為” 。

使用 PIVOT 和 UNPIVOT