Sql经典之数据表转置
数据库表格TableA,如何得到TableB呢?
TableA:
TableB:
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')
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')