07.oracle数据库的行转列

07.oracle数据库的行转列

-- 列转行
create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-10','胜');
insert into tmp values('2005-05-10','负');
insert into tmp values('2005-05-10','负');

select *
from tmp;

/*
          胜 负
2005-05-09 2 2
2005-05-10 1 2
*/

select *
from (
select rq,count(decode(shengfu,'胜','1')) 胜,count(decode(shengfu,'负','1')) 负
from tmp  group by rq) order by rq ;

create table STUDENT_SCORE
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);

insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

/*
姓名   语文  数学  英语
王五    89    56    89
*/

select *
from STUDENT_SCORE;

select name 姓名,max(decode(subject,'语文',score)) 语文,
       max(decode(subject,'数学',score)) 数学,
       max(decode(subject,'英语',score)) 英语
from STUDENT_SCORE group by name;


select t.*,t.语文+t.数学+t.英语 总分
from (select name 姓名,max(decode(subject,'语文',score)) 语文,
       max(decode(subject,'数学',score)) 数学,
       max(decode(subject,'英语',score)) 英语
from STUDENT_SCORE group by name) t;