数据库行列转换查询

行转列

行数据

select name,
max(case course when '语文' then score else 0 end)语文,
max(case course when '数学' then score else 0 end)数学,
max(case course when '英语' then score else 0 end)英语
from test1 group by name;  

转换后的列数据

列转行

如果刚开始是列

select user_name,'语文' COURSE,CN_SCORE as SCORE from test2 
union select user_name,'数学' COURSE,MATH_SCORE as SCORE from test2 
union select user_name,'英语' COURSE,EN_SCORE as SCORE from test2 order by user_name,COURSE; 

然后这时候就会转换成行