SQL列转换行的两种方法
标签：英语 ... sub 换行 http image case color end
SQL列转换行的两种方法
通过MAX...CASE...WHEN...转换列数据
select Student as ‘姓名‘,
MAX(CASE Subject WHEN ‘语文‘ THEN Score ELSE 0 END ) AS ‘语文‘, --替换Subject列数据为Score列数据
MAX(CASE Subject WHEN ‘英语‘ THEN Score ELSE 0 END ) AS ‘英语‘ --替换Subject列数据为Score列数据
from Scores
group by Student
order by Student
SELECT
Student as ‘姓名‘,
--通过AVG函数转换语文列
AVG(语文) as ‘语文‘,
--通过AVG函数转换英语列
AVG(英语) as ‘英语‘
from Scores
--转换英语和语文的数据为Score列对应的数据
pivot( AVG(Score) for Subject in (语文,英语) )as NewScores
group by Student
order by Student asc