oracle sql列转行_Oracle数据库中列转行的两种方法

1.创建tb_student表

create table tb_student(

id number(4) ,

name varchar2(20),

course varchar2(20),

score number(5,2)

);

insert into tb_student values(1,'张三','语文',81);

insert into tb_student values(2,'张三','数学',75);

insert into tb_student values(3,'李四','语文',86);

insert into tb_student values(4,'李四','数学',90);

insert into tb_student values(5,'王五','语文',81);

insert into tb_student values(6,'王五','数学',100);

insert into tb_student values(7,'王五','英语',90);

commit;

表如下图：

2.列转行第一种方法，代码见下：

select

name,

sum(decode(course, '数学', score)) 数学,

sum(decode(course, '英语', score)) 英语,

sum(decode(course, '语文', score)) 语文

from

tb_student

group by name;

3.列转行第二种方法，代码见下：

select

name,

sum(case course when '数学' then score else null end) 数学,

sum(case course when '英语' then score else null end) 英语,

sum(case course when '语文' then score else null end) 语文

from

tb_student

group by name;

最终结果如下图所示：

decode语法：

decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值)