思考以下问题,行列转换问题?
create table student_data (
id number primary key ,
name varchar2(32),
course varchar2(32),
score number(4,2)
);
insert into student_data values (1,’aaa’,'java’,88);
insert into student_data values (2,’aaa’,'c#’,98);
insert into student_data values (3,’bbb’,'java’,77);
insert into student_data values (4,’bbb’,'c#’,87);
commit;
转换前的数据:
id name course score
1 aaa java 88
2 aaa c# 98
3 bbb java 77
4 bbb c# 87
转换后的数据:
name java c#
aaa 88 98
bbb 77 87
方法1：
select t.name,sum(t.java) java , sum(t.c#) c#
from (select name,case when course = ‘java’ then score else 0 end java,
case when course = ‘c#’ then score else 0 end c#
from student_data ) t
group by t.name;
方法2：
select name,sum(case when course = ‘java’ then score else 0 end ) as java ,
sum(case when course = ‘c#’ then score else 0 end ) as c#
from student_data
group by name;
方法3：
select name ,sum(decode(course,’java’,score)) java, sum(decode(course,’c#’,score)) c#
from student_data
group by name;