SQLServer 2005 实现交叉表格报表的利器 PIVOT 和 UNPIVOT 关系运算符

在SQLServer 2000环境中,如果要实现交叉表格报表,主要是靠一系列复杂的 SELECT...CASE 语句.

其实现过程请参阅这里T-SQL 交叉报表(行列互换) 交叉查询 旋转查询

在SQLServer 2005中我们可以使用PIVOT关系运算符来实现行列转换.

还是以学生成绩表来举例:

id姓名 科目 成绩

1张三语文60
2张三数学65
3张三外语70
4李四语文80
5李四数学90
6李四外语85
7王五语文70
8王五数学71
9王五外语75
10赵六语文64
11赵六数学67
12赵六外语76

查询后得出:

姓名语文数学外语

李四80 90 85
王五70 71 75
张三60 65 70
赵六64 67 76

--准备数据:

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

go

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

drop table studentscore--删除与实验冲突的表

go

create table studentscore--创建实验表

(

[id] int identity(1,1),

[name] nvarchar(20) not null,

subject nvarchar(20) not null,

score int not null

)

go

select * from studentscore

go

--添加实验数据

insert studentscore values ('张三','语文','60');

insert studentscore values ('张三','数学','65');

insert studentscore values ('张三','外语','70');

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

insert studentscore values ('李四','数学','90');

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

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

insert studentscore values ('王五','数学','71');

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

insert studentscore values ('赵六','语文','64');

insert studentscore values ('赵六','数学','67');

insert studentscore values ('赵六','外语','76');

go

select * from studentscore

go

使用 SELECT...CASE 语句实现代码如下

select [name],

语文=max(case

when subject='语文' then score else 0

end),

数学=max(case

when subject='数学' then score else 0

end),

外语=max(case

when subject='外语' then score else 0

end)

from studentscore

group by [name]

结果:

下面我们使用PIVOT关系运算符来实现行列转换

select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'

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

pivot

(

sum(score) for subject in([语文],[数学],[外语])

) as pvt

结果:用较少的代码完成了交叉表格报表

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

对于这种方法要注意的一点是,我们使用sum()聚合函数,表面上没有指定按什么方式分组,但是自动按照name列分组了.

怎么做到的呢?原来pivot关系运算符会根据前面的对象中的列来自行判断,在这个例子中pivot前面的对象是ss,是个子查询,这个子查询中只有三列,score,subject和[name],但是pivot运算符内部使用了score和subject这两列,那么肯定是对[name]分组.

所以我们得出,pivot运算符的分组规则是,跟随对象中的那些不在pivot运算符内部的列:

为了好理解我们再写一个例子:

--在ss这个子查询中,多加一列id

--那么pivot应该按照name和id进行分组

select[name],[语文]as'语文',[数学]as'数学',[外语]as'外语'

from(selectscore,subject,[name],idfromstudentscore)asss

pivot

(

sum(score)forsubjectin([语文],[数学],[外语])

)aspvt

结果:验证了我们的设想

UNPIVOT关系运算符从字面上来看,就知道它的用途正好和PIVOT相反,下面举例说明:

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

drop table studentscore--删除与实验冲突的表

go

create table studentscore--创建实验表

(

[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

--添加实验数据

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

结果:

SELECT id, [name],subject, score

FROM

(SELECT id,[name], 语文=yuwen, 数学=shuxue, 外语=waiyu

FROM studentscore) as ss

UNPIVOT

(score FOR subject IN

(语文, 数学, 外语)

)AS unpvt

结果: