一、使用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ù)表的列轉(zhuǎn)行

1.在這里我們先構(gòu)建一個測試數(shù)據(jù)表(這里使用的是臨時表，以方便我們在退出會話的時候自動刪除表及其數(shù)據(jù))

首先我們先設計一個表架構(gòu)為#Student { 學生編號[PK],? 姓名, 性別, 所屬班級 }的表,然后編寫如下T-SQL

--創(chuàng)建臨時表(僅演示,表結(jié)構(gòu)的不合理還請包涵)

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班';

以下是查詢的結(jié)果：

2.查詢各班級的總?cè)藬?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ù) } 由 班級[行] 的顯示轉(zhuǎn)換為 班級[列] 的顯示格式!

在此你會看到第一個PIVOT示例。是否很期待??

3.編寫第一個PIVOT示例

SELECT?'班級總?cè)藬?shù):'?AS?[總?cè)藬?shù)],?[初一?1班],?[初一?2班],?[初二?1班],?[初三?1班],?[初三?2班]?
FROM?(
	SELECT?[所屬班級]?AS?[班級],?[姓名]?FROM?#Student)?AS?[SourceTable]?
	PIVOT?(COUNT([姓名])?FOR?[班級]?IN?([初一?1班],?[初一?2班],?[初二?1班],?[初三?1班],?[初三?2班])?)?AS?[PivotTable]

總?cè)藬?shù)?????????初一?1班???????初一?2班???????初二?1班???????初三?1班???????初三?2班
-----------?-----------?-----------?-----------?-----------?-----------
班級總?cè)藬?shù):??????4???????????0???????????5???????????3???????????2

在結(jié)果表中我們看到了對于不存在的班級初一2班它的總?cè)藬?shù)為0，這符合我們預期的結(jié)果!

解釋：使用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個透視列的值均為需要轉(zhuǎn)換為行的列的常量值,需要用[]括起，支持GUID，字符串及各種數(shù)字!

4.下面演示一個較為高級的行轉(zhuǎn)列的應用示例

--使用PIVOT查詢班級內(nèi)的男女學生人數(shù)及總?cè)藬?shù)

SELECT?[所屬班級]?AS?[班級],?[男]?AS?[男生人數(shù)],?[女]?AS?[女生人數(shù)],?[男]?+?[女]?AS?[總?cè)藬?shù)]?
FROM?(
	SELECT?[姓名],?[所屬班級],?[性別]?FROM?#Student)?AS?[SourceTable]?
	PIVOT?(COUNT([姓名])?FOR?[性別]?IN?([男],?[女]))?AS?[PivotTable]?
	ORDER?BY?[總?cè)藬?shù)]?DESC

班級???????男生人數(shù)????????女生人數(shù)????????總?cè)藬?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??'班級總?cè)藬?shù):'?AS?[總?cè)藬?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]

將多個列合并到單個列的轉(zhuǎn)換的語句!!!

--結(jié)果

SELECT??[班級],?[總?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?[所屬班級]?AS?[班級],[男]?AS?[男生人數(shù)],[男]?+?[女]?AS?[總?cè)藬?shù)]??
INTO?#PivotTable2?--放到臨時表方便查詢??
FROM?(??
	SELECT?[學生編號],?[所屬班級],?[性別]?FROM?#Student)?AS?[SourceTable]??
	PIVOT?(COUNT([學生編號])?FOR?[性別]?IN?([男],?[女]))?AS?[PivotTable]??
	ORDER?BY?[總?cè)藬?shù)]?DESC??
SELECT?[班級],[男生或女生人數(shù)],[性別],[總?cè)藬?shù)]
FROM?(??
	SELECT?[班級],?[男生人數(shù)],?[女生人數(shù)],?[總?cè)藬?shù)]?FROM?#PivotTable2)?AS?[s]??
	UNPIVOT?([男生或女生人數(shù)]FOR?[性別]?IN?([男生人數(shù)],[女生人數(shù)]))?AS?[un_p]

或者將性別和人數(shù)合并到一個列當中：

SELECT?[班級],[性別]?+?':?'?+?CAST([男生或女生人數(shù)]?AS?NVARCHAR(1))?AS?[男生或女生人數(shù)],[總?cè)藬?shù)]
FROM?(SELECT?[班級],?[男生人數(shù)],?[女生人數(shù)],?[總?cè)藬?shù)]?FROM?#PivotTable2)?AS?[s]??
UNPIVOT?([男生或女生人數(shù)]?FOR?[性別]?IN?([男生人數(shù)],?[女生人數(shù)]))?AS?[un_p]