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 (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,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
结果:
