行转列与列转行

1.行转列
–1）
decode

select sno,
sum(decode(cno, ‘c001’, score, 0)) 课程1,
sum(decode(cno, ‘c002’, score, 0)) 课程2,
max(decode(cno, ‘c003’, score, 0)) 课程3,
max(decode(cno, ‘c007’, score, 0)) 课程7,
max(decode(cno, ‘c010’, score, 0)) 课程10,
sum(score) 总成绩
from sc
group by sno
order by sno;

–2）
case when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end

select sno,
sum(case when cno= 'c001’then score else 0 end) 课程1,
sum(case when cno= 'c002’then score else 0 end) 课程2,
max(case when cno= 'c003’then score else 0 end) 课程3,
max(case when cno= 'c007’then score else 0 end) 课程7,
max(case when cno= 'c010’then score else 0 end) 课程10,
sum(score) 总成绩
from sc
group by sno
order by sno;

–3）
pivot

select t.*,nvl(t.课程1,0) + nvl(t.课程2,0) + nvl(t.课程3,0)+ nvl(t.课程7,0)+ nvl(t.课程10,0) 总成绩
from (select *
from sc
pivot(sum(nvl(score,0))
for cno in(‘c001’ 课程1, ‘c002’ 课程2, ‘c003’ 课程3,‘c007’ 课程7,‘c010’ 课程10))) t

2.列转行
unpivot

create table scc as
select sno,
sum(case when cno= 'c001’then score else 0 end) c001,
sum(case when cno= 'c002’then score else 0 end) c002,
max(case when cno= 'c003’then score else 0 end) c003,
max(case when cno= 'c007’then score else 0 end) c007,
max(case when cno= 'c010’then score else 0 end) c010
from sc
group by sno
order by sno;

select *
from (select *
from scc unpivot (score
for cno in(c001, c002,c003,c007,c010)));