http://msdn.microsoft.com/zh-cn/library/ms177410%28v=SQL.90%29.aspx
可以使用 PIVOT 和 UNPIVOT 關系運算符將表值表達式更改為另一個表。PIVOT 通過將表達式某一列中的唯一值轉換為輸出中的多個列來旋轉表值表達式,并在必要時對最終輸出中所需的任何其余列值執行聚合。UNPIVOT 與 PIVOT 執行相反的操作,將表值表達式的列轉換為列值。
注意:
對升級到 Microsoft SQL Server 2005 的數據庫使用 PIVOT 和 UNPIVOT 時,數據庫的兼容級別必須設置為 90。有關如何設置數據庫兼容級別的信息,請參閱 sp_dbcmptlevel (Transact-SQL) 。
PIVOT 提供的語法比一系列復雜的 SELECT...CASE 語句中所指定的語法更簡單和更具可讀性。有關 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>
下面的代碼示例生成一個兩列四行的表。
USE AdventureWorks ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture
下面是結果集:
DaysToManufacture????????? AverageCost
0????????????????????????? 5.0885
1????????????????????????? 223.88
2????????????????????????? 359.1082
4????????????????????????? 949.4105
沒有定義
DaysToManufacture
為 3 的產品。
以下代碼顯示相同的結果,該結果經過透視以使
DaysToManufacture
值成為列標題。提供一個列表示三
[3]
天,即使結果為
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
下面是結果集:
Cost_Sorted_By_Production_Days??? 0???????? 1???????? 2?????????? 3?????? 4??????
AverageCost?????????????????????? 5.0885??? 223.88??? 359.1082??? NULL??? 949.4105
可能會用到
PIVOT
的常見情況是:需要生成交叉表格報表以匯總數據。例如,假設需要在
AdventureWorks
示例數據庫中查詢
PurchaseOrderHeader
表以確定由某些特定雇員所下的采購訂單數。以下查詢提供了此報表(按供應商排序)。
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
以下為部分結果集。
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 語句返回的結果。
SELECT PurchaseOrderID, EmployeeID, VendorID FROM PurchaseOrderHeader
這意味著
EmployeeID
列返回的唯一值自行變成了最終結果集中的字段。結果,在透視子句中指定的每個
EmployeeID
號都有相應的一列:在本例中為雇員
164
、
198
、
223
、
231
和
233
。
PurchaseOrderID
列作為值列,將根據此列對最終輸出中返回的列(稱為分組列)進行分組。在本例中,通過
COUNT
函數聚合分組列。請注意,將顯示一條警告消息,指出為每個雇員計算
COUNT
時未考慮顯示在
PurchaseOrderID
列中的任何空值。
重要提示:
如果聚合函數與 PIVOT 一起使用,則計算聚合時將不考慮出現在值列中的任何空值。
UNPIVOT 將與 PIVOT 執行幾乎完全相反的操作,將列轉換為行。假設以上示例中生成的表在數據庫中存儲為
pvt
,并且您需要將列標識符
Emp1
、
Emp2
、
Emp3
、
Emp4
和
Emp5
旋轉為對應于特定供應商的行值。這意味著必須標識另外兩個列。包含要旋轉的列值(
Emp1
、
Emp2
...)的列將被稱為
Employee
,將保存當前位于待旋轉列下的值的列被稱為
Orders
。這些列分別對應于 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
以下為部分結果集。
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 會執行一次聚合,從而將多個可能的行合并為輸出中的單個行。而 UNPIVOT 不會重現原始表值表達式的結果,因為行已經被合并了。另外,UNPIVOT 的輸入中的空值不會顯示在輸出中,而在執行 PIVOT 操作之前,輸入中可能有原始的空值。
AdventureWorks 示例數據庫中的 Sales.vSalesPersonSalesByFiscalYears 視圖將使用 PIVOT 返回每個銷售人員在每個會計年度的總銷售額。若要在 SQL Server Management Studio 中編寫視圖腳本,請在 “對象資源管理器” 中,在 “視圖” 文件夾下找到 AdventureWorks 數據庫對應的視圖。右鍵單擊該視圖名稱,再選擇 “編寫視圖腳本為” 。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
