在SQL Server 2005中實作表的行列轉換
作者:張洪舉 Microsoft MVP
日期:2006年06月02日
應用于:Visual FoxPro 9.0 SP2
注:本文摘自作者的《循序漸進-SQL Server 2005入門、進階與應用執行個體》一書
PIVOT和UNPIVOT關系運算符是SQL Server 2005提供的新增功能,是以,對更新到SQL Server 2005的資料庫使用PIVOT和UNPIVOT時,資料庫的相容級别必須設定為90(可以使用sp_dbcmptlevel存儲過程設定相容級别)。
在查詢的FROM子句中使用PIVOT和UNPIVOT,可以對一個輸入表值表達式執行某種操作,以獲得另一種形式的表。PIVOT運算符将輸入表的行旋轉為列,并能同時對行執行聚合運算。而UNPIVOT運算符則執行與PIVOT運算符相反的操作,它将輸入表的列旋轉為行。
在FROM子句中使用PIVOT和UNPIVOT關系運算符時的文法格式如下:
[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
<pivoted_table> | <unpivoted_table>
}
<pivoted_table> ::=
table_source PIVOT <pivot_clause> table_alias
<pivot_clause> ::=
( aggregate_function ( value_column )
FOR pivot_column
IN ( <column_list> )
)
<unpivoted_table> ::=
table_source UNPIVOT <unpivot_clause> table_alias
<unpivot_clause> ::=
( value_column FOR pivot_column IN ( <column_list> ) )
<column_list> ::=
column_name [ , ... ]
table_source PIVOT <pivot_clause>
指定對table_source表中的pivot_column列進行透視。table_source可以是一個表、表表達式或子查詢。
aggregate_function
系統或使用者定義的聚合函數。注意:不允許使用COUNT(*)系統聚合函數。
value_column
PIVOT運算符用于進行計算的值列。與UNPIVOT一起使用時,value_column不能是輸入table_source中的現有列的名稱。
FOR pivot_column
PIVOT運算符的透視列。pivot_column必須是可隐式或顯式轉換為nvarchar()的類型。
使用UNPIVOT時,pivot_column是從table_source中提取輸出的列名稱,table_source中不能有該名稱的現有列。
IN ( column_list )
在PIVOT子句中,column_list列出pivot_column中将成為輸出表的列名的值。
在UNPIVOT子句中,column_list列出table_source中将被提取到單個pivot_column中的所有列名。
table_alias
輸出表的别名。
UNPIVOT < unpivot_clause >
指定将輸入表中由column_list指定的多個列的值縮減為名為pivot_column的單個列。
常見的可能會用到PIVOT的情形是:需要生成交叉表格報表以彙總資料。交叉表是使用較為廣泛的一種表格式,例如,圖5-4所示的産品銷售表就是一個典型的交叉表,其中的月份和産品種類都可以繼續添加。但是,這種格式在進行資料表存儲的時候卻并不容易管理,要存儲圖5-4這樣的表格資料,資料表通常需要設計為圖5-5這樣的結構。這樣就帶來一個問題,使用者既希望資料容易管理,又希望能夠生成一種能夠容易閱讀的表格資料。好在PIVOT為這種轉換提供了便利。
圖5-4 産品銷售表 圖5-5 資料表結構
假設Sales.Orders表中包含有ProductID(産品ID)、OrderMonth(銷售月份)和SubTotal(銷售額)列,并存儲有如表5-2所示的内容。
表5-2 Sales.Orders表中的内容
ProductID | OrderMonth | SubTotal |
---|---|---|
1 | 5 | 100.00 |
1 | 6 | 100.00 |
2 | 5 | 200.00 |
2 | 6 | 200.00 |
2 | 7 | 300.00 |
3 | 5 | 400.00 |
3 | 5 | 400.00 |
執行下面的語句:
SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月
FROM
Sales.Orders PIVOT
(
SUM (Orders.SubTotal)
FOR Orders.OrderMonth IN
( [5], [6], [7] )
) AS pvt
ORDER BY ProductID;
在上面的語句中,Sales.Orders是輸入表,Orders.OrderMonth是透視列(pivot_column),Orders.SubTotal是值列(value_column)。上面的語句将按下面的步驟獲得輸出結果集:
a.PIVOT首先按值列之外的列(ProductID和OrderMonth)對輸入表Sales.Orders進行分組彙總,類似執行下面的語句:
SELECT ProductID, OrderMonth,
SUM (Orders.SubTotal) AS SumSubTotal
FROM Sales.Orders
GROUP BY ProductID, OrderMonth;
這時候将得到一個如表5-3所示的中間結果集。其中隻有ProductID為3的産品由于在5月有2筆銷售記錄,被累加到了一起(值為800)。
表5-3 Sales.Orders表經分組彙總後的結果
ProductID | OrderMonth | SumSubTotal |
---|---|---|
1 | 5 | 100.00 |
1 | 6 | 100.00 |
2 | 5 | 200.00 |
2 | 6 | 200.00 |
2 | 7 | 300.00 |
3 | 5 | 800.00 |
b.PIVOT根據FOR Orders.OrderMonth IN指定的值5、6、7,首先在結果集中建立名為5、6、7的列,然後從圖5-3所示的中間結果中取出OrderMonth列中取出相符合的值,分别放置到5、6、7的列中。此時得到的結果集的别名為pvt(見語句中AS pvt的指定)。結果集的内容如表5-4所示。
表5-4 使用FOR Orders.OrderMonth IN( [5], [6], [7] )後得到的結果集
ProductID | 5 | 6 | 7 |
---|---|---|---|
1 | 100.00 | 100.00 | NULL |
2 | 200.00 | 200.00 | 200.00 |
3 | 800.00 | NULL | NULL |
c.最後根據SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月FROM的指定,從别名pvt結果集中檢索資料,并分别将名為5、6、7的列在最終結果集中重新命名為五月、六月、七月。這裡需要注意的是FROM的含義,其表示從經PIVOT關系運算符得到的pvt結果集中檢索資料,而不是從Sales.Orders中檢索資料。最終得到的結果集如表5-5所示。
表5-5 由表5-2所示的Sales.Orders表将行轉換為列得到的最終結果集
ProductID | 五月 | 六月 | 七月 |
---|---|---|---|
1 | 100.00 | 100.00 | NULL |
2 | 200.00 | 200.00 | 200.00 |
3 | 800.00 | NULL | NULL |
UNPIVOT與PIVOT執行幾乎完全相反的操作,将列轉換為行。但是,UNPIVOT并不完全是PIVOT的逆操作,由于在執行PIVOT過程中,資料已經被進行了分組彙總,是以使用UNPIVOT并不會重制原始表值表達式的結果。假設表5-5所示的結果集存儲在一個名為MyPvt的表中,現在需要将列辨別符“五月”、“六月”和“七月”轉換到對應于相應産品ID的行值(即傳回到表5-3所示的格式)。這意味着必須另外辨別兩個列,一個用于存儲月份,一個用于存儲銷售額。為了便于了解,仍舊分别将這兩個列命名為OrderMonth和SumSubTotal。參考下面的語句:
CREATE TABLE MyPvt (ProductID int, 五月int, 六月 int, 七月int); --建立MyPvt表
GO
--将表5-5中所示的值插入到MyPvt表中
INSERT INTO MyPvt VALUES (1,100,100,0);
INSERT INTO MyPvt VALUES (2,200,200,200);
INSERT INTO MyPvt VALUES (3,800,0,0);
--執行UNPIVOT
SELECT ProductID, OrderMonth, SubTotal
FROM
MyPvt UNPIVOT
(SubTotal FOR OrderMonth IN (五月, 六月, 七月)) AS unpvt;
上面的語句将按下面的步驟獲得輸出結果集:
a.首先建立一個臨時結果集的結構,該結構中包含MyPvt表中除IN (五月, 六月, 七月)之外的列,以及SubTotal FOR OrderMonth中指定的值列(SubTotal)和透視列(OrderMonth)。
b.将在MyPvt中逐行檢索資料,将表的列名稱(在IN (五月, 六月, 七月)中指定)放入OrderMonth列中,将相應的值放入到SubTotal列中。最後得到的結果集如表5-6所示。
表5-6 使用UNPIVOT得到的結果集
ProductID | OrderMonth | SubTotal |
---|---|---|
1 | 五月 | 100 |
1 | 六月 | 100 |
1 | 七月 | |
2 | 五月 | 200 |
2 | 六月 | 200 |
2 | 七月 | 200 |
3 | 五月 | 800 |
3 | 六月 | |
3 | 七月 |