Oracle行转列（待续）

1、先建立测试表

create table studentscore
(
  student NVARCHAR2(50),
  course NVARCHAR2(50),
  score number
);

 

2、插入测试数据

insert into studentscore(student,course,score) values('路人甲','语文',80);
insert into studentscore(student,course,score) values('路人甲','数学',90);
insert into studentscore(student,course,score) values('路人甲','英语',100);
insert into studentscore(student,course,score) values('路人乙','语文',85);
insert into studentscore(student,course,score) values('路人乙','数学',95);

3、行转列

(a)、固定列

select a.student as "学生",

sum(decode(a.course,'语文',a.score,null))"语文",

sum(decode(a.course,'数学',a.score,null))"数学",

sum(decode(a.course,'英语',a.score,null))"英语"

from studentscore a

group by a.student;

（b)、动态列

创建存储过程

create or replace procedure W_TEST as
sqlstr varchar2(1000);

begin
sqlstr:='select student 学生';
for cur1 in (select distinct course from studentscore  order by course)
loop
  sqlstr:=sqlstr||',
    sum(decode(course, '''||cur1.course||''',score,null)) '||cur1.course;
end loop;
sqlstr:=sqlstr||'
  from studentscore
  group by student';
  sqlstr := 'CREATE OR REPLACE VIEW V_RESULT  AS '||  sqlstr;
EXECUTE IMMEDIATE sqlstr;
end W_TEST;

执行存储过程

begin
  w_test;
end;

执行视图

SELECT * FROM V_RESULT;

4、结果如下图