--初始化环境
create table grade(
Code int not null,
Name varchar(10),
Scores int)
--测试数据
insert into grade
select 1, 'A', 100
union select 1, 'B', 90
union select 1, 'C', 85
union select 1, 'D', 60
union select 2, 'A', 92
union select 2, 'C', 72
union select 3, 'B', 83
--测试
declare @sql varchar(8000)
set @sql = 'select code '
select @sql = @sql + ', sum(case [Name] when ''' + [Name] + ''' then scores else 0 end) as ['+ [name] + ']'
from grade group by Name
select @sql = @sql + ' from grade group by code'
print @sql
exec (@sql)
--删除
drop table grade
create table grade(Code int not null,Name varchar(10),Scores int)--测试数据insert into grade select 1, 'A', 100 union select 1, 'B', 90union select 1, 'C', 85union select 1, 'D', 60union select 2, 'A', 92union select 2, 'C', 72union select 3, 'B', 83--测试declare @sql varchar(8000)set @sql = 'select code 'select @sql = @sql + ', sum(case [Name] when ''' + [Name] + ''' then scores else 0 end) as ['+ [name] + ']'from grade group by Nameselect @sql = @sql + ' from grade group by code'print @sqlexec (@sql)--删除drop table grade