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运算符内部的列:

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

?

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

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

pivot

(

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

)?as?pvt

?

结果:验证了我们的设想

?

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

?

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

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

go

create table studentscore--创建实验表

(

[id] int identity(1,

yuwen int not null,

shuxue int not null,

waiyu int not null

)

go

?

select * from studentscore

go

?

--添加实验数据

insert studentscore values ('张三','60','65','80','90','86');

insert studentscore values ('王五','70','71','64','67','76');

go

select * from studentscore

go

?

结果:?

SELECT id, [name], score

FROM

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

?? FROM studentscore) as ss

UNPIVOT

?? (score FOR subject IN

????? (语文, 数学, 外语)

)AS unpvt

结果:

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!