固定列数的行列转换,表结构为:
转换后:
要求:创建表,源表,表名:student , 只用一句sql 得到转换结果。
解答:
方法一 :通过生成临时表的方式操作
select name ,sum(yw) as '语文',sum(sx) as '数学',sum(wy) as '英语'
from(
select name ,
CASE subject WHEN '语文' THEN score END AS yw,
CASE subject WHEN '数学' THEN score END AS sx,
CASE subject WHEN '英语' THEN score END AS wy
from student
) tempStudent
group by name
方法二:课程只有语文、数学、物理这三门课程则可以使用静态sql 来实现
select name as 姓名,
max(case subject when '语文' then score else 0 end) 语文,
max(case subject when '数学' then score else 0 end) 数学,
max(case subject when '英语' then score else 0 end) 英语
from student
group by name