SQL Server 行列相互轉換命令：PIVOT和UNPIVOT使用詳解
一、使用PIVOT和UNPIVOT命令的SQL Server版本要求
1.數(shù)據(jù)庫的最低版本要求為SQL Server 2005 或更高。
2.必須將數(shù)據(jù)庫的兼容級別設置為90 或更高。
3.查看我的數(shù)據(jù)庫版本及兼容級別。
如果不知道怎么看數(shù)據(jù)庫版本或兼容級別的話可以在SQL Server Management Studio新建一個查詢窗口輸入：print @@version，運行之后在我的本機上得到：
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr? 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.2
然后我們選擇一個數(shù)據(jù)庫然后右鍵-屬性 選擇[選項]得到下圖的信息。
?
在確認數(shù)據(jù)庫的版本和兼容級別符合1,2點的要求后你才可以接著繼續(xù)往下學習。
二、使用PIVOT 實現(xiàn)數(shù)據(jù)表的列轉行
1.在這里我們先構建一個測試數(shù)據(jù)表(這里使用的是臨時表，以方便我們在退出會話的時候自動刪除表及其數(shù)據(jù))
首先我們先設計一個表架構為#Student { 學生編號[PK],? 姓名, 性別, 所屬班級 }的表,然后編寫如下T-SQL
--創(chuàng)建臨時表(僅演示,表結構的不合理還請包涵)
CREATE?TABLE?#Student?([學生編號]?INT?IDENTITY(1,?1)?PRIMARY?KEY,?[姓名]?NVARCHAR(20),?[性別]?NVARCHAR(1),?[所屬班級]?NVARCHAR(20))
--給臨時表插入數(shù)據(jù)
INSERT?INTO?#Student?([姓名],?[性別],?[所屬班級])? SELECT?'李妹妹',?'女',?'初一?1班'?UNION?ALL? SELECT?'泰強',?'男',?'初一?1班'?UNION?ALL? SELECT?'泰映',?'男',?'初一?1班'?UNION?ALL? SELECT?'何謝',?'男',?'初一?1班'?UNION?ALL? SELECT?'李春',?'男',?'初二?1班'?UNION?ALL? SELECT?'吳歌',?'男',?'初二?1班'?UNION?ALL? SELECT?'林純',?'男',?'初二?1班'?UNION?ALL? SELECT?'徐葉',?'女',?'初二?1班'?UNION?ALL? SELECT?'龍門',?'男',?'初三?1班'?UNION?ALL? SELECT?'小紅',?'女',?'初三?1班'?UNION?ALL? SELECT?'小李',?'男',?'初三?1班'?UNION?ALL? SELECT?'小黃',?'女',?'初三?2班'?UNION?ALL? SELECT?'旺財',?'男',?'初三?2班'?UNION?ALL? SELECT?'強強',?'男',?'初二?1班';
以下是查詢的結果：
2.查詢各班級的總人數(shù)
SELECT?[所屬班級]?AS?[班級],?COUNT(1)?AS?[人數(shù)]?FROM?#Student?GROUP?BY?[所屬班級]?ORDER?BY?[人數(shù)]?DESC
班級???????人數(shù) --------?----------- 初二?1班????5 初一?1班????4 初三?1班????3 初三?2班????2
好了，在這里我希望把上面的表{ 班級, 人數(shù) } 由 班級[行] 的顯示轉換為 班級[列] 的顯示格式!
在此你會看到第一個PIVOT示例。是否很期待??
3.編寫第一個PIVOT示例
SELECT?'班級總人數(shù):'?AS?[總人數(shù)],?[初一?1班],?[初一?2班],?[初二?1班],?[初三?1班],?[初三?2班]? FROM?( SELECT?[所屬班級]?AS?[班級],?[姓名]?FROM?#Student)?AS?[SourceTable]? PIVOT?(COUNT([姓名])?FOR?[班級]?IN?([初一?1班],?[初一?2班],?[初二?1班],?[初三?1班],?[初三?2班])?)?AS?[PivotTable] 總人數(shù)?????????初一?1班???????初一?2班???????初二?1班???????初三?1班???????初三?2班 -----------?-----------?-----------?-----------?-----------?----------- 班級總人數(shù):??????4???????????0???????????5???????????3???????????2
在結果表中我們看到了對于不存在的班級初一2班它的總人數(shù)為0，這符合我們預期的結果!
解釋：使用POVIT首先你需要在FROM子句內(nèi)定義2個表：
A.一個稱為源表(SourceTable)。
B.另一個稱為數(shù)據(jù)透視表(PivotTable)。
語法：
SELECT?,? [第一個透視列]?AS?,? [第二個透視列]?AS?,? ...? [最后一個透視列]?AS?? FROM?()?AS??PIVOT?(()?FOR?[]?IN?([第一個透視列],?[第二個透視列],...?[最后一個透視列]))?AS?? ;
以上的PIVOT子句內(nèi)的第1…n個透視列的值均為需要轉換為行的列的常量值,需要用[]括起，支持GUID，字符串及各種數(shù)字!
4.下面演示一個較為高級的行轉列的應用示例
--使用PIVOT查詢班級內(nèi)的男女學生人數(shù)及總人數(shù)
SELECT?[所屬班級]?AS?[班級],?[男]?AS?[男生人數(shù)],?[女]?AS?[女生人數(shù)],?[男]?+?[女]?AS?[總人數(shù)]? FROM?( SELECT?[姓名],?[所屬班級],?[性別]?FROM?#Student)?AS?[SourceTable]? PIVOT?(COUNT([姓名])?FOR?[性別]?IN?([男],?[女]))?AS?[PivotTable]? ORDER?BY?[總人數(shù)]?DESC 班級???????男生人數(shù)????????女生人數(shù)????????總人數(shù) --------?-----------?-----------?----------- 初二?1班????4???????????1???????????5 初一?1班????3???????????1???????????4 初三?1班????2???????????1???????????3 初三?2班????1???????????1???????????2
三、使用UNPIVOT 實現(xiàn)的功能其實與PIVOT恰恰相反
1.語法同PIVOT但是UNPIVOT的子句沒有聚合函數(shù)
SELECT?? ,?? [合并后的列]?AS?,?? [行值的列名]?AS?? FROM?()?AS?? UNPIVOT?(?? ? FOR??IN?(?? [第一個合并列],?[第二個合并列],?? ...?? [最后一個合并列]?? )?? )?AS?? ;
2.看上面的語法感覺很浮云，不怕，這里帶例子(繼續(xù)使用II中用到的PIVOT表)
--源表
SELECT??'班級總人數(shù):'?AS?[總人數(shù)],??[初一?1班],?[初一?2班],??[初二?1班],??[初三?1班],?[初三?2班]?? INTO?#PivotTable?--為了使表達意圖更清晰,我把PIVOT處理后的表放到一個臨時表當中?? FROM?( SELECT?? [所屬班級]?AS?[班級],?? [學生編號]?? FROM?#Student?? )?AS?[SourceTable]?? PIVOT?(COUNT([學生編號])?FOR?[班級]?IN?([初一?1班],?[初一?2班],?[初二?1班],?[初三?1班],?[初三?2班]?)?)?AS?[PivotTable]
將多個列合并到單個列的轉換的語句!!!
--結果
SELECT??[班級],?[總人數(shù)]?? FROM?( SELECT??[初一?1班],?[初一?2班],[初二?1班],?[初三?1班],?[初三?2班] FROM??#PivotTable?)?AS?[s]?? UNPIVOT?([總人數(shù)],[初一?1班],[初一?2班],[初二?1班],[初三?1班],?[初三?2班]))?AS?[un_p]
?
執(zhí)行下面代碼：
SELECT?[所屬班級]?AS?[班級],[男]?AS?[男生人數(shù)],[男]?+?[女]?AS?[總人數(shù)]?? INTO?#PivotTable2?--放到臨時表方便查詢?? FROM?(?? SELECT?[學生編號],?[所屬班級],?[性別]?FROM?#Student)?AS?[SourceTable]?? PIVOT?(COUNT([學生編號])?FOR?[性別]?IN?([男],?[女]))?AS?[PivotTable]?? ORDER?BY?[總人數(shù)]?DESC?? SELECT?[班級],[男生或女生人數(shù)],[性別],[總人數(shù)] FROM?(?? SELECT?[班級],?[男生人數(shù)],?[女生人數(shù)],?[總人數(shù)]?FROM?#PivotTable2)?AS?[s]?? UNPIVOT?([男生或女生人數(shù)]FOR?[性別]?IN?([男生人數(shù)],[女生人數(shù)]))?AS?[un_p]
或者將性別和人數(shù)合并到一個列當中：
SELECT?[班級],[性別]?+?':?'?+?CAST([男生或女生人數(shù)]?AS?NVARCHAR(1))?AS?[男生或女生人數(shù)],[總人數(shù)] FROM?(SELECT?[班級],?[男生人數(shù)],?[女生人數(shù)],?[總人數(shù)]?FROM?#PivotTable2)?AS?[s]?? UNPIVOT?([男生或女生人數(shù)]?FOR?[性別]?IN?([男生人數(shù)],?[女生人數(shù)]))?AS?[un_p]
下載文檔
