动态SQL

create table marks (stuno int,subject varchar(20),score float)
insert into marks values(1,'英语',68)
insert into marks values(1,'数学',93)
insert into marks values(1,'语文',77)
insert into marks values(2,'英语',75)
insert into marks values(2,'数学',95)
insert into marks values(2,'语文',82)
insert into marks values(2,'体育',82)

 

 

declare @strsql varchar(2000)
set @strsql='select stuno '
select @strsql=@strsql+',sum( case subject when '''+subject +''' then score end ) '
from (select distinct subject from marks) a
set @strsql=@strsql+' from marks group by stuno'
print @strsql
exec (@strsql)

 

/*

拼接的结果

当向数据库添加科目时，自动统计科目
 select stuno 
,sum( case subject when '数学' then score end ) 数学
,sum( case subject when '体育' then score end ) 体育
,sum( case subject when '英语' then score end ) 英语
,sum( case subject when '语文' then score end ) 语文

*/