select
[Student],
[数学]=max(casewhen[Course]=数学then[Score]else0end),
[物理]=max(casewhen[Course]=物理then[Score]else0end),
[英语]=max(casewhen[Course]=英语then[Score]else0end),
[语文]=max(casewhen[Course]=语文then[Score]else0end)
from
Class
groupby[Student]
select
[Student],
[数学]=max(casewhen[Course]=数学then[Score]else0end),
[物理]=max(casewhen[Course]=物理then[Score]else0end),
[英语]=max(casewhen[Course]=英语then[Score]else0end),
[语文]=max(casewhen[Course]=语文then[Score]else0end),
[总成就]=sum([Score])--加多一列(学科均匀分用avg([Score]))
from
Class
groupby[Student]
ifnotobject_id(Class)isnull
droptableClass
Go
CreatetableClass([Student]nvarchar(2),[数学]int,[物理]int,[英语]int,[语文]int)
InsertClass
selectN李四,77,85,65,65unionall
selectN张三,87,90,82,78
Go