SQL_行转列

1. 背景

在某个面试中做到了行转列的笔试题，因为无数据测试，当时写的也不确定，所以创建了一个用例测试了一下。数据如下图（比较简单），要做的是得到一个新表，列分别是ID、math_score、english_score、python_score、sql_score

2. 代码

代码如下图：

select ID,
max(case subject when 'math' then score else 0 end) as math_score,
max(case subject when 'english' then score else 0 end) as english_score,
max(case subject when 'python' then score else 0 end) as python_score,
max(case subject when 'sql' then score else 0 end) as sql_score
from sc
group by ID

SELECT * FROM sc pivot ( max( score ) FOR sub IN ([math],[english],[python],[sql]) 
) AS a