在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表中的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 七月 |
在上面的語句中,Sales.Orders是輸入表,Orders.OrderMonth是透視列(pivot_column),Orders.SubTotal是值列(value_column)。上面的語句將按下面的步驟獲得輸出結果集:
a.PIVOT首先按值列之外的列(ProductID和OrderMonth)對輸入表Sales.Orders進行分組彙總,類似執行下面的語句:
SELECT ProductID, |
這時候將得到一個如表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表 來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/66009/viewspace-842228/,如需轉載,請註明出處,否則將追究法律責任。
上一篇:
sql server分組查詢示例
下一篇:
SQL Server日期計算
請登入後發表評論
登入
全部評論
最新文章
|
相關文章
- SQL Server 實現行列(縱橫表)轉換SQLServer
- sql 實現表的行列轉換SQL
- SQL Server 2005之PIVOT/UNPIVOT行列轉換(轉)SQLServer
- sql server 行列轉換SQLServer
- sql server行列轉換案例SQLServer
- oracle11g pivot 行列轉換 SQL Server 2005OracleSQLServer
- 在Word中實現表格的行列互換 (轉)
- 在報表中錄入資料時如何實現行列轉換
- SQL Server中行列轉換 Pivot UnPivotSQLServer
- SQL Server 2005下的行列轉化(簡單)SQLServer
- 行列轉換sqlSQL
- 試驗Oracle中實現行列轉換的方法(轉)Oracle
- 用abap實現內表的行列轉換-原始碼2 (轉)原始碼
- Oracle--SQL行列轉換實戰OracleSQL
- sql行列轉置的實現方法SQL
- sql語句實現表的行列倒置SQL
- 行列轉換 交叉表 (轉)
- SQL Server 2005中各個系統表的作用(轉)SQLServer
- 【SQL 學習】行列轉換SQL
- 【SQL】行列轉換方法示例SQL
- 在SQL Server 2005資料庫中實現自動備份SQLServer資料庫
- pivot、unpivot實現oracle行列轉換Oracle
- SQL2000和2005下行列轉換例項SQL
- 報表如何實現行列互換效果?
- SQL Server 2005中的DDL觸發器的實現SQLServer觸發器
- SQL 如何實現動態的行列轉置SQL
- Spark實現行列轉換pivot和unpivotSpark
- 使用vue實現行列轉換的一種方法。Vue
- WORD及EXCEL行列轉換實現方法收藏(轉)Excel
- 在Excel中怎麼快速進行行列轉換?Excel
- 在 SQL Server 2005 中使用表值函式來實現空間資料庫SQLServer函式資料庫
- 用ORACLE分析函式實現行列轉換Oracle函式
- SQL Server 2005快速Web分頁的實現SQLServerWeb
- SQL Server 2005 中的分割槽表和索引應用SQLServer索引
- SQL Server 2005中修改 Server Collation的方法SQLServer
- 行列轉換
- 在SQL SERVER中實現事務的部分回滾SQLServer
- SQL SERVER 2005表分割槽功能SQLServer