Oracle 行转列和列转行

一、行转列

1.1、初始测试数据

表结构：TEST_TB_GRADE

Sql代码:

?

1 2 3 4 5 6 7

create table TEST_TB_GRADE (    ID        NUMBER(10) not null ,    USER_NAME VARCHAR2(20 CHAR ),    COURSE    VARCHAR2(20 CHAR ),    SCORE     FLOAT )

初始数据如下图：  

insert into test_tb_grade values(1, 'aa', 'Math', 80.0);
insert into test_tb_grade values(2, 'aa', 'English', 90.0);
insert into test_tb_grade values(3, 'bb', 'Math', 70.0);
insert into test_tb_grade values(4, 'bb', 'English', 60.0);
commit;

1.2、 如果需要实现如下的查询效果图：

  

这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的，具体的sql如下：

Sql代码:

SELECT user_name,
  SUM(DECODE(course, 'Math', score, NULL))    AS MATH,
  SUM(DECODE(course, 'English', score, NULL)) AS ENGLISH
FROM test_tb_grade
GROUP BY user_name
ORDER BY user_name;

?

1.3、延伸

如果要实现对各门功课的不同分数段进行统计，效果图如下：

      

具体的实现sql如下：
Sql代码:

SELECT t2.SCORE_GP,
  SUM(DECODE(t2.course,'Math', COUNTNUM,NULL))    AS MATH,
  SUM(DECODE(t2.course,'English', COUNTNUM,NULL)) AS ENGLISH
FROM
  (SELECT t.course,
    CASE
      WHEN t.score<60
      THEN '00-60'
      WHEN t.score>=60
      AND t.score  <80
      THEN '60-80'
      WHEN t.score>=80
      THEN '80-100'
    END            AS SCORE_GP,
    COUNT(t.score) AS COUNTNUM
  FROM test_tb_grade t
  GROUP BY t.course,
    CASE
      WHEN t.score<60
      THEN '00-60'
      WHEN t.score>=60
      AND t.score  <80
      THEN '60-80'
      WHEN t.score>=80
      THEN '80-100'
    END
  ORDER BY t.course
  ) t2
GROUP BY t2.SCORE_GP
ORDER BY t2.SCORE_GP;

二、列转行

1.1、初始测试数据

表结构：  TEST_TB_GRADE2  
Sql代码:   

?

1 2 3 4 5 6 7 8

create table TEST_TB_GRADE2 (    ID         NUMBER(10) not null ,    USER_NAME  VARCHAR2(20 CHAR ),    CN_SCORE   FLOAT ,    MATH_SCORE FLOAT ,    EN_SCORE   FLOAT )

初始数据如下图 ：

  insert into TEST_TB_GRADE2 values(1, 'aa', 50, 60, 70);
  insert into TEST_TB_GRADE2 values(1, 'bb', 60, 70, 80);
  insert into TEST_TB_GRADE2 values(1, 'cc', 80, 90, 100);
  insert into TEST_TB_GRADE2 values(1, 'dd', 100, 90, 80);
  commit;

1.2、 如果需要实现如下的查询效果图：

 

这就是最常见的列转行，主要原理是利用SQL里面的union，具体的sql语句如下：
Sql代码: 

SELECT user_name, 
  'CN_SCORE' AS COURSE, 
  CN_SCORE AS SCORE 
FROM test_tb_grade2
UNION
SELECT user_name,
  'MATH_SCORE' AS COURSE,
  MATH_SCORE   AS SCORE
FROM test_tb_grade2
UNION
SELECT user_name,
  'EN_SCORE' AS COURSE,
  EN_SCORE   AS SCORE
FROM test_tb_grade2
ORDER BY user_name,COURSE;

?

也可以利用【insert all into ... select】来实现，首先需要先建一个表TEST_TB_GRADE3：

Sql代码:   

?

1 2 3 4 5 6

create table TEST_TB_GRADE3   (         USER_NAME VARCHAR2(20 CHAR ),          COURSE    VARCHAR2(20 CHAR ),          SCORE     FLOAT   )

   
 再执行下面的sql： 

  Sql代码:  

INSERT ALL
INTO test_tb_grade3
  (
    USER_NAME,
    COURSE,
    SCORE
  )
  VALUES
  (
    user_name,
    'Chinese',
    CN_SCORE
  )
INTO test_tb_grade3
  (
    USER_NAME,
    COURSE,
    SCORE
  )
  VALUES
  (
    user_name,
    'Math',
    MATH_SCORE
  )
INTO test_tb_grade3
  (
    USER_NAME,
    COURSE,
    SCORE
  )
  VALUES
  (
    user_name,
    'English',
    EN_SCORE
  )
SELECT user_name, CN_SCORE, MATH_SCORE, EN_SCORE FROM test_tb_grade2;
COMMIT;

 别忘记commit操作，然后再查询TEST_TB_GRADE3，发现表中的数据就是列转成行了。