在SQL Server 2005中實現表的行列轉換

來源:互聯網
上載者:User

  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



相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

11.11 Big Sale for Cloud

Get Unbeatable Offers with up to 90% Off,Oct.24-Nov.13 (UTC+8)

Get It Now >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。