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)));
