交叉表查询解决办法
xxj 2003-03-31 05:55:27 关于交叉表查询:
比如说学生成绩表的记录如下:
姓名 科目 成绩
徐侠君 语文 60
徐侠君 数学 90
徐侠君 英语 58
徐侠君 计算机软件 80
李寻欢 语文 80
李寻欢 数学 95
李寻欢 英语 0
李寻欢 武术 99
韦小保 俄语 50
韦小保 数学 65
韦小保 英语 0
韦小保 武术 40
现在要得到如下结果
姓名 语文 数学 英语 计算机软件 武术 俄语
徐侠君 60 90 58 80 null null
李寻欢 80 95 0 null 99 null
韦小保 null 65 0 null 40 50
由此可见交叉表查询就是把纵向的记录变为横向的字段
在Access有专门的语句TransForm实现这种转换
TransForm,大家可以使用ACCESS建立一个交叉表查询试图
但是我却没有找到MS SQL相应的语法
不得不自己写一个存储过程实现这种令我们恶心但是用户却开心的功能:
请先执行以下语句,产生示例数据
Create Table Score(
Name varchar(50) not null,
Subject varchar(50) not null,
Score float(8) null
)
go
alter table Score add CONSTRAINT
PK_Score PRIMARY KEY clustered (Name, Subject)
go
insert into Score(Name,Subject,Score) values('李寻欢','数学',80)
go
insert into Score(Name,Subject,Score) values('李寻欢','武术',95)
go
insert into Score(Name,Subject,Score) values('李寻欢','英语',0)
go
insert into Score(Name,Subject,Score) values('李寻欢','语文',80)
go
insert into Score(Name,Subject,Score) values('韦小保','俄语',50)
go
insert into Score(Name,Subject,Score) values('韦小保','数学',65)
go
insert into Score(Name,Subject,Score) values('韦小保','武术',40)
go
insert into Score(Name,Subject,Score) values('韦小保','英语',0)
go
insert into Score(Name,Subject,Score) values('徐侠君','计算机软件',80)
go
insert into Score(Name,Subject,Score) values('徐侠君','数学',90)
go
insert into Score(Name,Subject,Score) values('徐侠君','英语',60)
go
insert into Score(Name,Subject,Score) values('徐侠君','语文',60)
go
接着
执行下面的匿名SQL语句
alter procedure GetScoreReport
as
declare @sql varchar(8000)
declare @ls_id varchar(50)
begin
set @SQL = 'select Name as 姓名,'
declare C1 Cursor for
select distinct Subject
from Score
open C1
fetch next from C1 into @ls_id
while @@fetch_status=0
begin
select @sql = @sql + 'sum(case Subject when '''+@ls_id+''' then Score else 0 end) as '+@ls_id+','
fetch next from C1 into @ls_id
end
select @sql=substring(@sql,1,len(@sql)-1)+' from Score group by Name'
exec(@sql)
close c1
DEALLOCATE C1
end
GO