select
students.*,
语文=max(case when Course='语文' then Score else 0 end),
数学=max(case when Course='数学' then Score else 0 end),
外语=max(case when Course='外语' then Score else 0 end),
总成绩=sum(Score)
from
students,object where students.stuid=object.stuid
group by students.stuid,students.stuname,students.age,students.sex怎么转换成动态语句
students.*,
语文=max(case when Course='语文' then Score else 0 end),
数学=max(case when Course='数学' then Score else 0 end),
外语=max(case when Course='外语' then Score else 0 end),
总成绩=sum(Score)
from
students,object where students.stuid=object.stuid
group by students.stuid,students.stuname,students.age,students.sex怎么转换成动态语句
set @s=''
Select @s=@s+','+quotename([Course])+'=sum(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (sum([Score]) for [Course] in('+@s+'))b')
[/code]
这个是2k5及以上可用,lz参考