--测试环境
create table #(id int,xh varchar(10),kmmc varchar(20),fs int)
insert into # select 1,'01601','思想品德',88
union all select 2,'01601','语文',98
union all select 3,'01601','数学',99--动态SQL
declare @s varchar(200)
set @s=''
select @s=@s+',['+kmmc+']=max(case when kmmc='''+kmmc+''' then fs end )'
from #
group by kmmc
set @s='select '+stuff(@s,1,1,'')+' from # '
--print @s
exec(@s)
--结果
数学 思想品德 语文
----------- ----------- -----------
99 88 98
create table #(id int,xh varchar(10),kmmc varchar(20),fs int)
insert into # select 1,'01601','思想品德',88
union all select 2,'01601','语文',98
union all select 3,'01601','数学',99--动态SQL
declare @s varchar(200)
set @s=''
select @s=@s+',['+kmmc+']=max(case when kmmc='''+kmmc+''' then fs end )'
from #
group by kmmc
set @s='select '+stuff(@s,1,1,'')+' from # '
--print @s
exec(@s)
--结果
数学 思想品德 语文
----------- ----------- -----------
99 88 98
set @s = ''select
@s = @s + ',['+KCMC+']=max(case KCMC when '''+KCMC+''' then FS end)'
from
XS_CJ
group by
KCMCset @s = 'select XH'+@s+' from XS_CJ group by XH'
exec(@s)
思想品德 语文 数学 ....
88 98 99....思想品德 语文 数学也是一条记录。