http://msdn.microsoft.com/zh-cn/library/ms177410%28v=SQL.90%29.aspx
可以使用 PIVOT 和 UNPIVOT 關(guān)系運(yùn)算符將表值表達(dá)式更改為另一個(gè)表。PIVOT 通過(guò)將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來(lái)旋轉(zhuǎn)表值表達(dá)式，并在必要時(shí)對(duì)最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT 與 PIVOT 執(zhí)行相反的操作，將表值表達(dá)式的列轉(zhuǎn)換為列值。
注意：
對(duì)升級(jí)到 Microsoft SQL Server 2005 的數(shù)據(jù)庫(kù)使用 PIVOT 和 UNPIVOT 時(shí)，數(shù)據(jù)庫(kù)的兼容級(jí)別必須設(shè)置為 90。有關(guān)如何設(shè)置數(shù)據(jù)庫(kù)兼容級(jí)別的信息，請(qǐng)參閱 sp_dbcmptlevel (Transact-SQL) 。
PIVOT 提供的語(yǔ)法比一系列復(fù)雜的 SELECT...CASE 語(yǔ)句中所指定的語(yǔ)法更簡(jiǎn)單和更具可讀性。有關(guān) PIVOT 語(yǔ)法的完整說(shuō)明，請(qǐng)參閱 FROM (Transact-SQL) 。
以下是帶批注的 PIVOT 語(yǔ)法。
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>
下面的代碼示例生成一個(gè)兩列四行的表。
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
沒(méi)有定義
DaysToManufacture
為 3 的產(chǎn)品。
以下代碼顯示相同的結(jié)果，該結(jié)果經(jīng)過(guò)透視以使
DaysToManufacture
值成為列標(biāo)題。提供一個(gè)列表示三
[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
可能會(huì)用到
PIVOT
的常見(jiàn)情況是：需要生成交叉表格報(bào)表以匯總數(shù)據(jù)。例如，假設(shè)需要在
AdventureWorks
示例數(shù)據(jù)庫(kù)中查詢(xún)
PurchaseOrderHeader
表以確定由某些特定雇員所下的采購(gòu)訂單數(shù)。以下查詢(xún)提供了此報(bào)表（按供應(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 語(yǔ)句返回的結(jié)果。
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader
這意味著
EmployeeID
列返回的唯一值自行變成了最終結(jié)果集中的字段。結(jié)果，在透視子句中指定的每個(gè)
EmployeeID
號(hào)都有相應(yīng)的一列：在本例中為雇員
164
、
198
、
223
、
231
和
233
。
PurchaseOrderID
列作為值列，將根據(jù)此列對(duì)最終輸出中返回的列（稱(chēng)為分組列）進(jìn)行分組。在本例中，通過(guò)
COUNT
函數(shù)聚合分組列。請(qǐng)注意，將顯示一條警告消息，指出為每個(gè)雇員計(jì)算
COUNT
時(shí)未考慮顯示在
PurchaseOrderID
列中的任何空值。
重要提示：
如果聚合函數(shù)與 PIVOT 一起使用，則計(jì)算聚合時(shí)將不考慮出現(xiàn)在值列中的任何空值。
UNPIVOT 將與 PIVOT 執(zhí)行幾乎完全相反的操作，將列轉(zhuǎn)換為行。假設(shè)以上示例中生成的表在數(shù)據(jù)庫(kù)中存儲(chǔ)為
pvt
，并且您需要將列標(biāo)識(shí)符
Emp1
、
Emp2
、
Emp3
、
Emp4
和
Emp5
旋轉(zhuǎn)為對(duì)應(yīng)于特定供應(yīng)商的行值。這意味著必須標(biāo)識(shí)另外兩個(gè)列。包含要旋轉(zhuǎn)的列值（
Emp1
、
Emp2
...）的列將被稱(chēng)為
Employee
，將保存當(dāng)前位于待旋轉(zhuǎn)列下的值的列被稱(chēng)為
Orders
。這些列分別對(duì)應(yīng)于 Transact-SQL 定義中的
pivot_column
和
value_column
。以下為該查詢(xún)。
--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
...
請(qǐng)注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 會(huì)執(zhí)行一次聚合，從而將多個(gè)可能的行合并為輸出中的單個(gè)行。而 UNPIVOT 不會(huì)重現(xiàn)原始表值表達(dá)式的結(jié)果，因?yàn)樾幸呀?jīng)被合并了。另外，UNPIVOT 的輸入中的空值不會(huì)顯示在輸出中，而在執(zhí)行 PIVOT 操作之前，輸入中可能有原始的空值。
AdventureWorks 示例數(shù)據(jù)庫(kù)中的 Sales.vSalesPersonSalesByFiscalYears 視圖將使用 PIVOT 返回每個(gè)銷(xiāo)售人員在每個(gè)會(huì)計(jì)年度的總銷(xiāo)售額。若要在 SQL Server Management Studio 中編寫(xiě)視圖腳本，請(qǐng)?jiān)? “對(duì)象資源管理器” 中，在 “視圖” 文件夾下找到 AdventureWorks 數(shù)據(jù)庫(kù)對(duì)應(yīng)的視圖。右鍵單擊該視圖名稱(chēng)，再選擇 “編寫(xiě)視圖腳本為” 。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
