Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
go
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
go
name 数学 英语 语文
---------- ----------- ----------- -----------
李四 85 78 78
张三 86 75 80
declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 end)
['+课程名称+']'
from (select distinct 课程名称 from view_stu ) as t
select @sql = @sql+' from
view_stu temp group by 学号,姓名'
exec(@sql)
as select a.*,b.课程名称 from 成绩 a,课程 b where a.课程代号=b.课程代号
然后:
declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 end)
['+课程名称+']'
from (select distinct 课程名称 from view_stu ) as t
select @sql = @sql+' from
view_stu temp group by 学号,姓名'
exec(@sql)
这样做是为了减少代码的复杂度。
set @s=''
select @s=@s+',['+convert(varchar(10),课程名称)+']=sum(case 课程名称 when '''+课程名称+''' then 成绩 else 0 end)'
from (select distinct 课程名称 from 课程表) a
exec('select a.学号,姓名'+@s+' from 学生成绩表 a join 课程表 b on a.学号=b.学号 group by a.学号')