sql行列转换学习记录

表1

表2

 

数据准备：

create table hzlb(
       username varchar2(10),
       subject varchar2(10),
       score number
);
insert into hzlb values('张三','语文',80);
insert into hzlb values('张三','数学',90);
insert into hzlb values('张三','英语',70);
insert into hzlb values('张三','生物',85);
insert into hzlb values('李四','语文',80);
insert into hzlb values('李四','数学',92);
insert into hzlb values('李四','英语',76);
insert into hzlb values('李四','生物',88);
insert into hzlb values('码农','语文',60);
insert into hzlb values('码农','数学',82);
insert into hzlb values('码农','英语',96);
insert into hzlb values('码农','生物',78);


create table lzhb(
       username varchar2(10),
       数学 number,
       语文 number,
       英语 number,
       生物 number
);


insert into lzhb values('张三',90,80,70,85);
insert into lzhb values('李四',92,80,76,88);
insert into lzhb values('码农',82,60,96,78);
commit;


select * from hzlb;
select * from lzhb;

将表一转为表二：

select username,
     sum(case when subject='数学' then score  end ) "数学",
     sum(case when subject='语文' then score else 0 end) as "语文",
     sum(case when subject='英语' then score else 0 end) as "英语",
     sum(case when subject='生物' then score else 0 end) as "生物"
from hzlb
group by username;

要点：分组，聚合函数可以将多列聚合为一个值，因此去掉了空值或0

将表二转为表一

select username, '数学' "subject", 数学 "score"    from lzhb
union 
select username, '语文' "subject", 语文 "score"    from lzhb
union 
select username, '英语' "subject", 英语 "score"    from lzhb
union 
select username, '生物' "subject", 生物 "score"    from lzhb;

要点：可以直接添加常数列，如''生物'，可使整列都有这个汉字