一、行转列
1) 使用decode函数
select name,
sum(decode(course, ‘语文’, score, 0)) as 语文,
sum(decode(course, ‘数学’, score, 0)) as 数学,
sum(decode(course, ‘英语’, score, 0)) as 英语
from GRADE group by name;
2) 使用case when语句
select name,
sum(case course when ‘语文’ then score else 0 end) as 语文,
sum(case course when ‘数学’ then score else 0 end) as 数学,
sum(case course when ‘英语’ then score else 0 end) as 英语
from GRADE group by name;
运行结果:
二、列转行
select name, ‘语文’ [as] course, cn_score [as] score from SC_GRADE
union all
select name, ‘数学’ [as] course, math_score [as] score from SC_GRADE
union all
select name, ‘英语’ [as] course, en_score [as] score from SC_GRADE
order by name;
以语文作为值填充列名为course的列,以cn_score作为值填充列名为score的列
运行结果: