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

例一：

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

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

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

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

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é)果集的列名.

如果我們將其中的一月改為四月，因為數(shù)據(jù)源中沒有四月的記錄，所以四月查詢出來應(yīng)該為Null.
測試：

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)境中,如果要實現(xiàn)交叉表格報表,主要是靠一系列復(fù)雜的 SELECT...CASE 語句.

其實現(xiàn)過程請參閱這里T-SQL 交叉報表(行列互換) 交叉查詢 旋轉(zhuǎn)查詢

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

還是以學(xué)生成績表來舉例:

id姓名 科目 成績

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

查詢后得出:

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

李四?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--刪除與實驗沖突的表

go

create?table?studentscore--創(chuàng)建實驗表

(

[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ù)據(jù)

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

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

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

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

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

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

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

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

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

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

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

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

go

select?*?from?studentscore

go

?

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

select?[name],

語文=max(case

when?subject='語文'?then?score?else?0

end),

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

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

end),

外語=max(case

when?subject='外語'?then?score?else?0

end)

from?studentscore

group?by?[name]

結(jié)果:

?

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

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

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

pivot

(

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

)?as?pvt

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

?

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

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

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

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

為了好理解我們再寫一個例子:

--在ss這個子查詢中,多加一列id

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

?

?

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

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

pivot

(

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

)?as?pvt

?

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

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

?

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

drop?table?studentscore--刪除與實驗沖突的表

go

create?table?studentscore--創(chuàng)建實驗表

(

[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ù)據(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],?語文=yuwen,?數(shù)學(xué)=shuxue,?外語=waiyu

???FROM?studentscore)?as?ss

UNPIVOT

???(score?FOR?subject?IN

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

)AS?unpvt

結(jié)果:

?