在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
结果:
