第一部分:行转列
1、新建一张表:
create table score(
id int not null,
name varchar2(10) not null,
subject varchar2(50) not null,
score int
);
2、插入测试数据:
insert into score values(1,'张三','语文',80);
insert into score values(1,'张三','数学',85);
insert into score values(1,'张三','英语',90);
insert into score values(2,'lisi','语文',70);
insert into score values(2,'lisi','数学',55);
insert into score values(2,'lisi','英语',60);
insert into score values(3,'王五','语文',20);
insert into score values(3,'王五','数学',30);
insert into score values(3,'王五','英语',40);
3、列出数据如下:
4、转换数据如下:
5、转换方式一:
通过decode来转换:
select id,name,
sum(decode(subject,'语文',score,null)) "语文",
sum(decode(subject,'数学',score,null)) "数学",
sum(decode(subject,'英语',score,null)) "英语"
from score group by id,name;
6、转换方式二:
通过case when 表达式转换:
select id,name,
sum(case when subject='语文' then score end) "语文",
sum(case when subject='数学' then score end) "数学",
sum(case when subject='英语' then score end) "英语"
from score group by id,name;
第二部分:列转行:
1、建表语句:
create table scores(
id int primary key,
name varchar2(10) not null,
chinese int,
math int,
english int
)
2、列出数据如下:
3、转换出数据如下:
4、转换方式:
a、集合查询:
select id,name,'语文' course,chinese score from scores
union
select id,name,'数学' course,math score from scores
union
select id,name,'英语' course,english score from scores;
b、insert all方式:
create table scores_result(
id int,
name varchar2(10) not null,
course varchar2(10) not null,
score int
);
insert all
into scores_result values(id,name,'语文',chinese)
into scores_result values(id,name,'数学',math)
into scores_result values(id,name,'英语',english)
select id,name,chinese,math,english from scores;