MS-SQLServer 2000 T-SQL 交叉报表(行列互换) 交叉查询 旋转查询

在MS-SQLServer 2005 中可以使用pivot运算符来来实现行列转换. ,但在以下版本中必须使用 case when then else end 语句

 

下面以学生成绩表来举例:

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

 

 

我们先利用case when then else end 语句将行转为列:

select  [ name ],语文 = case  when subject = ' 语文 '  then score  else  0  end  from studentscore  group  by  [ name ],subject,score

这里为了好理解只取一列,得到下面的结果

 

有了语文成绩行专列的例子后,我们很容易将其他两列也添加进来,

 

select  [ name ],
语文 = case 
when subject = ' 语文 '  then score  else  0
end,
数学 = case 
when subject = ' 数学 '  then score  else  0
end,
外语 = case 
when subject = ' 外语 '  then score  else  0
end
from studentscore
group  by  [ name ],subject,score

下面是查询后的结果:

 

现在只要把name相同的行合并到一起就ok了,

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 ]

 

好了,看看结果吧.

 

上面是列数可枚举时的代码,很多情况下到底有多少列是动态的或者不可知的.

这个时候需要拼下SQL语句了.

 

declare  @sql  varchar( 8000)

set  @sql  =  ' select [name], '
select  @sql  =  @sql  +  ' sum(case subject when  ''' +subject + '''  
then score else 0 end) as  ''' +subject + ''' , '
from ( select  distinct subject  from studentscore)  as a 
select  @sql  =  left( @sql, len( @sql) - 1)  +  '  from studentscore group by [name] '

print  @sql  -- 查看生成的sql代码

exec( @sql)

 

 打印生成的sql代码如下:

 

这个语句还可以再优化些,呵呵.各位大虾有么有更好的方案?

end

 

转自:http://blog.youkuaiyun.com/ylqmf/article/details/4438383