Oracle行列转换

在oracle中通常会遇到这样的问题，需要将行转换成列，如图：

    

 

  

一般通常用的函数有以下三种：

 decode：

 case when：

 pivot：

 --oracle 实现行转列
  --创建测试表
  CREATE TABLE student_score(
  name varchar2(20),
  subject varchar2(20),
  score number(4,1)
  );
  --插入测试数据
  INSERT INTO student_score (name,subject,score) values('张三','语文',89);
  INSERT INTO student_score (name,subject,score) values('张三','数学',79);
  INSERT INTO student_score (name,subject,score) values('张三','英语',91);
  INSERT INTO student_score (name,subject,score) values('李四','语文',90);
  INSERT INTO student_score (name,subject,score) values('李四','数学',99);
  INSERT INTO student_score (name,subject,score) values('李四','英语',59);
  INSERT INTO student_score (name,subject,score) values('王五','语文',79);
  INSERT INTO student_score (name,subject,score) values('王五','数学',100);
  INSERT INTO student_score (name,subject,score) values('王五','英语',80);
  INSERT INTO student_score (name,subject,score) values('赵六','语文',86);
  INSERT INTO student_score (name,subject,score) values('赵六','数学',88);
  INSERT INTO student_score (name,subject,score) values('赵六','英语',89);
 --decode 实现行转列
 select name as "姓名" ,
   sum(decode(subject,'语文',nvl(score,0),0)) as "语文" ,
   sum(decode(subject,'数学',nvl(score,0),0)) as "数学",
   sum(decode(subject,'英语',nvl(score,0),0)) as "英语" 
   from student_score
     group by name; 
     
  --case when 行转列
  select name as "姓名",
         sum(case when subject='语文'
             then nvl(score,0)
              else 0
              end ) as "语文",
         sum(case when subject='数学'
             then nvl(score,0)
              else 0
              end )as "数学",
          sum(case when subject='英语'
             then nvl(score,0)
              else 0
              end )as "英语"
         from student_score 
         group by name;
   --pivot 实现行转列
   select * from (select name,subject,score from student_score group by name,subject,score)
    pivot(
       sum(score) for subject in('语文'as "语文"，'数学' as "数学"，'英语' as "英语")
    );