create table s(sn nvarchar(10),sname nvarchar(10))
insert into s select '001','wang'
insert into s select '002','zhang'
insert into s select '003','li'
create table c(cn nvarchar(10),cname nvarchar(10))
insert into c select '1001','y'
insert into c select '1002','s'
insert into c select '1003','w'
create table sc(sn nvarchar(10),cn nvarchar(10),grade int)
insert into sc select '001','1001',100
insert into sc select '001','1002',98
insert into sc select '001','1003',99
insert into sc select '002','1001',90
insert into sc select '002','1002',94
insert into sc select '002','1003',88
insert into sc select '003','1001',70
insert into sc select '003','1002',99
insert into sc select '003','1003',99
go
declare @sql varchar(1000)
set @sql='select s.sname,'
select @sql=@sql+char(13)+'sum(case c.cn when '+c.cn+' then sc.grade else '''' end) as ''' +c.cname+''','
from c
select @sql = left(@sql,len(@sql)-1) + char(13)+ 'from s,c,sc where s.sn=sc.sn and sc.cn=c.cn group by s.sname'
exec(@sql)
go