oracle行转列遇到的一个问题

create table SC(S# varchar2(10),C# varchar2(10),score float)   建表

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
commit;

 插入数据

运行：

select s# ,max(case c# when '01' then score else 0 end) '01',
max(case c# when '02' then score else 0 end)  '02'，
max(case c# when '03' then score else 0 end)  '03'，
avg(score)
from sc
group by s#
order by avg(score) desc

 报错

经过试验  如果列别名开头有数字的话  要用双引号引起来才不会报错

要这样：

select s# ,max(decode(C#,'01',score,0))"01",
max(decode(C#,'02',score,0))  "02"，
max(decode(C#,'03',score,0))  "03"，
avg(score)
from sc
group by s#
order by avg(score) desc

转载于:https://www.cnblogs.com/dopeboy/p/9543225.html