如何在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 七月 0 2 五月 200 2 六月 200 2 七月 200 3 五月 800 3 六月 0 3 七月 0