行列转换和活跃度计算

行列转换

建表

create table student2(
    id int,
    name varchar2(20),
    course varchar2(20),
    score int
);

数据：

insert into student2 values (1,'Tom','Chinese',80);
insert into student2 values (1,'Tom','Math',90);
insert into student2 values (1,'Tom','English',70);
insert into student2 values (2,'Bob','Chinese',90);
insert into student2 values (2,'Bob','Math',88);
insert into student2 values (2,'Bob','English',60);
insert into student2 values (3,'Jack','Chinese',96);
insert into student2 values (3,'Jack','Math',84);
insert into student2 values (3,'Jack','English',68);

行转列

select id,
       name,
       sum(case when course = 'Chinese' then score end) "Chinese",
       sum(case when course = 'Math' then score end)    "Math",
       sum(case when course = 'English' then score end) "English"
from student2
group by id, name;

将行转列代码生成新表

create table student3 as
select id,
       name,
       sum(case when course = 'Chinese' then score end) "Chinese",
       sum(case when course = 'Math' then score end)    "Math",
       sum(case when course = 'English' then score end) "English"
from student2
group by id, name;

列转行

select id, name, "Chinese" as score, 'Chinese' as course
from student3
union
select id, name, "Math" as score, 'Math' as course
from student3
union
select id, name, "English" as score, 'English' as course
from student3;