例一： 在Sql Server的幫助文檔中，對(duì)Pivot函數(shù)是這樣解釋的： 可以使用 PIVOT 和 UNPIVOT 關(guān)系運(yùn)算符對(duì)表表達(dá)式進(jìn)行操作以獲得另一個(gè)表。PIVOT 通過(guò)將表達(dá)式某一列中的唯一轉(zhuǎn)換為輸出中的多個(gè)列來(lái)轉(zhuǎn)換表表達(dá)式，并在必要時(shí)對(duì)最終輸出中所需的任何其余的列執(zhí)

例一：

在Sql Server的幫助文檔中，對(duì)Pivot函數(shù)是這樣解釋的：
可以使用 PIVOT 和 UNPIVOT 關(guān)系運(yùn)算符對(duì)表值表達(dá)式進(jìn)行操作以獲得另一個(gè)表。PIVOT 通過(guò)將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來(lái)轉(zhuǎn)換表值表達(dá)式，并在必要時(shí)對(duì)最終輸出中所需的任何其余的列值執(zhí)行聚合.

對(duì)第一次使用PIVOT函數(shù)的朋友來(lái)說(shuō)，這樣的解釋很難讓大家理解，下面編輯用PIVOT函數(shù)來(lái)實(shí)現(xiàn)一個(gè)行轉(zhuǎn)列的功能，以便讓讀者更容易理解該函數(shù).

注意：PIVOT是Sql Server2005的新函數(shù)，2005前行轉(zhuǎn)列請(qǐng)參看本站：
SQLServer中(行列轉(zhuǎn)換)行轉(zhuǎn)列及列轉(zhuǎn)行且加平均值及匯總值

先創(chuàng)建一個(gè)工資表:

Create Table Salary
(
HrName varchar(50),
Monthly varchar(50),
Money money
)

往表中插入數(shù)據(jù)：

insert into Salary(HrName,Monthly,[Money])?
select '張三','一月','3000'?
union all
select '張三','二月','3200'?
union all
select '張三','三月','3500'?
union all
select '李四','一月','3800'?
union all
select '李四','二月','4200'?
union all
select '李四','三月','3900'
union all
select '張三','一月','2000'

查看正常的數(shù)據(jù)：

select * from Salary

結(jié)果：

HrName? Monthly Money
張三??? 一月??? 3000.00
張三??? 二月??? 3200.00
張三??? 三月??? 3500.00
李四??? 一月??? 3800.00
李四??? 二月??? 4200.00
李四??? 三月??? 3900.00
張三??? 一月??? 2000.00

查看行轉(zhuǎn)列后的數(shù)據(jù)：

select HrName as '姓名',[一月],[二月],[三月] from Salary?
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) as pvt

結(jié)果：

姓名?? 一月???? 二月???? 三月
李四? 3800.00?? 4200.00? 3900.00
張三? 5000.00?? 3200.00? 500.00

注意：
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) 中的sum([Money])，這里必須是聚合函數(shù)，比如是min,max等。
in ([一月],[二月],[三月])中的[一月],[二月],[三月]即為Monthly的Value,又為新結(jié)果集的列名.

如果我們將其中的一月改為四月，因?yàn)閿?shù)據(jù)源中沒(méi)有四月的記錄，所以四月查詢出來(lái)應(yīng)該為Null.
測(cè)試：

select HrName as '姓名',[四月],[二月],[三月] from Salary?
pivot(sum([Money]) for Monthly in ([四月],[二月],[三月])) as pvt

結(jié)果：

姓名?? 四月??? 二月???? 三月
李四?? NULL?? 4200.00?? 3900.00
張三?? NULL?? 3200.00?? 3500.00

例二：

在SQLServer 2000環(huán)境中,如果要實(shí)現(xiàn)交叉表格報(bào)表,主要是靠一系列復(fù)雜的 SELECT...CASE 語(yǔ)句.

其實(shí)現(xiàn)過(guò)程請(qǐng)參閱這里T-SQL 交叉報(bào)表(行列互換) 交叉查詢 旋轉(zhuǎn)查詢

在SQLServer 2005中我們可以使用PIVOT關(guān)系運(yùn)算符來(lái)實(shí)現(xiàn)行列轉(zhuǎn)換.

還是以學(xué)生成績(jī)表來(lái)舉例:

id姓名 科目 成績(jī)

1?張三?語(yǔ)文?60
2?張三?數(shù)學(xué)?65
3?張三?外語(yǔ)?70
4?李四?語(yǔ)文?80
5?李四?數(shù)學(xué)?90
6?李四?外語(yǔ)?85
7?王五?語(yǔ)文?70
8?王五?數(shù)學(xué)?71
9?王五?外語(yǔ)?75
10?趙六?語(yǔ)文?64
11?趙六?數(shù)學(xué)?67
12?趙六?外語(yǔ)?76

查詢后得出:

姓名?語(yǔ)文數(shù)學(xué)外語(yǔ)

李四?80? 90? 85
王五?70? 71? 75
張三?60? 65? 70
趙六?64? 67? 76

--準(zhǔn)備數(shù)據(jù):

?

select?*?from?sysobjects?where?[xtype]='u'

go

if?exists(select?id?from?sysobjects?where?name='studentscore')

drop?table?studentscore--刪除與實(shí)驗(yàn)沖突的表

go

create?table?studentscore--創(chuàng)建實(shí)驗(yàn)表

(

[id]?int?identity(1,1),

[name]?nvarchar(20)?not?null,

subject?nvarchar(20)?not?null,

score?int?not?null

)

go

?

select?*?from?studentscore

go

?

--添加實(shí)驗(yàn)數(shù)據(jù)

insert?studentscore?values?('張三','語(yǔ)文','60');

insert?studentscore?values?('張三','數(shù)學(xué)','65');

insert?studentscore?values?('張三','外語(yǔ)','70');

insert?studentscore?values?('李四','語(yǔ)文','80');

insert?studentscore?values?('李四','數(shù)學(xué)','90');

insert?studentscore?values?('李四','外語(yǔ)','85');

insert?studentscore?values?('王五','語(yǔ)文','70');

insert?studentscore?values?('王五','數(shù)學(xué)','71');

insert?studentscore?values?('王五','外語(yǔ)','75');

insert?studentscore?values?('趙六','語(yǔ)文','64');

insert?studentscore?values?('趙六','數(shù)學(xué)','67');

insert?studentscore?values?('趙六','外語(yǔ)','76');

go

select?*?from?studentscore

go

?

使用 SELECT...CASE 語(yǔ)句實(shí)現(xiàn)代碼如下

select?[name],

語(yǔ)文=max(case

when?subject='語(yǔ)文'?then?score?else?0

end),

數(shù)學(xué)=max(case

when?subject='數(shù)學(xué)'?then?score?else?0

end),

外語(yǔ)=max(case

when?subject='外語(yǔ)'?then?score?else?0

end)

from?studentscore

group?by?[name]

結(jié)果:

?

下面我們使用PIVOT關(guān)系運(yùn)算符來(lái)實(shí)現(xiàn)行列轉(zhuǎn)換

select?[name],[語(yǔ)文]?as?'語(yǔ)文',[數(shù)學(xué)]?as?'數(shù)學(xué)',[外語(yǔ)]?as?'外語(yǔ)'

from?(select?score,subject,[name]?from?studentscore)?as?ss

pivot

(

sum(score)?for?subject?in([語(yǔ)文],[數(shù)學(xué)],[外語(yǔ)])

)?as?pvt

結(jié)果:用較少的代碼完成了交叉表格報(bào)表

?

============================

對(duì)于這種方法要注意的一點(diǎn)是,我們使用sum()聚合函數(shù),表面上沒(méi)有指定按什么方式分組,但是自動(dòng)按照name列分組了.

怎么做到的呢?原來(lái)pivot關(guān)系運(yùn)算符會(huì)根據(jù)前面的對(duì)象中的列來(lái)自行判斷,在這個(gè)例子中pivot前面的對(duì)象是ss,是個(gè)子查詢,這個(gè)子查詢中只有三列,score,subject和[name],但是pivot運(yùn)算符內(nèi)部使用了score和subject這兩列,那么肯定是對(duì)[name]分組.

所以我們得出,pivot運(yùn)算符的分組規(guī)則是,跟隨對(duì)象中的那些不在pivot運(yùn)算符內(nèi)部的列:

為了好理解我們?cè)賹懸粋€(gè)例子:

--在ss這個(gè)子查詢中,多加一列id

--那么pivot應(yīng)該按照name和id進(jìn)行分組

?

?

select?[name],[語(yǔ)文]?as?'語(yǔ)文',[數(shù)學(xué)]?as?'數(shù)學(xué)',[外語(yǔ)]?as?'外語(yǔ)'

from?(select?score,subject,[name],id?from?studentscore)?as?ss

pivot

(

sum(score)?for?subject?in([語(yǔ)文],[數(shù)學(xué)],[外語(yǔ)])

)?as?pvt

?

結(jié)果:驗(yàn)證了我們的設(shè)想

UNPIVOT關(guān)系運(yùn)算符從字面上來(lái)看,就知道它的用途正好和PIVOT相反,下面舉例說(shuō)明:

?

if?exists(select?id?from?sysobjects?where?name='studentscore')

drop?table?studentscore--刪除與實(shí)驗(yàn)沖突的表

go

create?table?studentscore--創(chuàng)建實(shí)驗(yàn)表

(

[id]?int?identity(1,1),

[name]?nvarchar(20)?not?null,

yuwen?int?not?null,

shuxue?int?not?null,

waiyu?int?not?null

)

go

?

select?*?from?studentscore

go

?

--添加實(shí)驗(yàn)數(shù)據(jù)

insert?studentscore?values?('張三','60','65','70');

insert?studentscore?values?('李四','80','90','86');

insert?studentscore?values?('王五','70','71','75');

insert?studentscore?values?('趙六','64','67','76');

go

select?*?from?studentscore

go

?

結(jié)果:?

?

?

?

SELECT?id,?[name],subject,?score

FROM

???(SELECT?id,[name],?語(yǔ)文=yuwen,?數(shù)學(xué)=shuxue,?外語(yǔ)=waiyu

???FROM?studentscore)?as?ss

UNPIVOT

???(score?FOR?subject?IN

??????(語(yǔ)文,?數(shù)學(xué),?外語(yǔ))

)AS?unpvt

結(jié)果:

?