TableA :
userid | subj | score |
張三 | chinese | 78 |
李四 | chinese | 89 |
張三 | english | 90 |
張三 | maths | 75 |
李四 | maths | 97 |
userid | chinese | maths | english |
李四 | 89 | 97 | 0 |
張三 | 78 | 75 | 90 |
sql语句如下:
Declare @s varchar(8000)
Set @s=''
Select @s=@s+',['+subj+']=sum(case subj when '''+subj+''' then score else 0 end)' from TableA group by subj
Exec('select userid' + @s + ' from TableA group by userid order by userid')