oracle行转列练习

----------------------第一题---------------------------
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);

--emp
/*
显示格式：  
       语文              数学                英语  
       及格              优秀                不及格   
*/
--方法一
select  name,
                (select score from student_score s1 where subject = '语文' and s1.name=s.name) 语文,
                (select score from student_score s1 where subject = '数学' and s1.name=s.name) 数学,
                (select score from student_score s1 where subject = '英语' and s1.name=s.name) 英语
  from student_score s group by name
 


--方法二 decode
select s.name,
       sum(decode(subject, '语文',s.score,0)) 语文,
       sum(decode(subject, '数学',s.score,0)) 数学,
       sum(decode(subject, '英语',s.score,0)) 英语
  from student_score s
 group by s.name
--方法三 case when
select s.name , sum(case s.subject when '语文' then s.score else 0 end) "语文", 
sum(case s.subject when '数学' then s.score else 0 end) 数学,
sum(case s.subject when '英语' then s.score else 0 end) 英语
from student_score s group by s.name
--方法四
采用 join表连接的方式



--判断及格否
select t.name 名字,
       case
         when t.y between 90 and 100 then
          '优秀' 
          when t.y between 60 and 90 then
          '及格'
          when t.y between 0 and 60 then
          '不及格'
       end 语文,
      case
         when t.s between 90 and 100 then
          '优秀' 
          when t.s between 60 and 90 then
          '及格'
          when t.s between 0 and 60 then
          '不及格'
       end 数学,
       case
         when t.e between 90 and 100 then
          '优秀' 
          when t.e between 60 and 90 then
          '及格'
          when t.e between 0 and 60 then
          '不及格'
       end 英语

  from (
        
        select s.name,
                 sum(decode(subject, '语文', s.score, 0)) y,
                sum(decode(subject, '数学', s.score, 0)) s,
                sum(decode(subject, '英语', s.score, 0)) e
          from student_score s
         group by s.name) t


         
-----------------------第二题--------------------------------
           
create table test(
   id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(5)
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');
/*
姓名      性别     年龄
--------- -------- ----
张三       男        50
*/



--方法一
--1
select listagg(decode(t.type, 1, t.value)) within group(order by value) 姓名,
       listagg(decode(t.type, 2, t.value)) within group(order by value) 性别,
       listagg(decode(t.type, 3, t.value)) within group(order by value) 年龄
  from test t
 group by t.t_id
 
 --方法二
 select max(decode(t.type, 1, t.value)) 姓名,
       max(decode(t.type, 2, t.value)) 性别,
      max(decode(t.type, 3, t.value))年龄
  from test t group by t.t_id
 
 
 --方法三表连接方式
 select * from test
 
select *
  from (select value name,t_id from test where type = 1) m1
  join (select value sex,t_id from test where type = 2) m2
    on m1.t_id = m2.t_id
 
 -------------------------第三题-------------------------
 
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 rq,
        sum(decode(shengfu, '胜', 1, '负', 0)) 胜,
        sum(decode(shengfu, '胜', 0, '负', 1)) 负
   from tmp
  group by rq