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