oracle数据库，行列转换

1.所需表的建立与插入数据

无论是在工作中还是在面试中，常常有一类问题经常出现：

依据现有数据，将以行（列）展示的数据转换为以列（行）的形式展示

测试表：SCORE_1/SCORE_2 

--建表  插数据

CREATE TABLE SCORE_1(STUDENT VARCHAR2(12),COURSE VARCHAR2(7),SCORE NUMBER);

SELECT * FROM SCORE_1;

INSERT INTO SCORE_1 VALUES('张三','CHINESE',70);

INSERT INTO SCORE_1 VALUES('张三','ENGLISH',90);

INSERT INTO SCORE_1 VALUES('张三','MATH',80);

INSERT INTO SCORE_1 VALUES('李四','CHINESE',76);

INSERT INTO SCORE_1 VALUES('李四','ENGLISH',91);

INSERT INTO SCORE_1 VALUES('李四','MATH',82);

INSERT INTO SCORE_1 VALUES('王五','CHINESE',85);

INSERT INTO SCORE_1 VALUES('王五','ENGLISH',70);

INSERT INTO SCORE_1 VALUES('王五','MATH',90);

COMMIT;

SELECT * FROM SCORE_1;   --插入数据之后查看一下表，

CREATE TABLE SCORE_2(STUDENT VARCHAR2(12),CHINESE NUMBER,MATH NUMBER,ENGLISH NUMBER);

INSERT INTO SCORE_2 VALUES('张三',70,80,90);

INSERT INTO SCORE_2 VALUES('李四',76,82,91);

INSERT INTO SCORE_2 VALUES('王五',85,90,70);

SELECT * FROM SCORE_2;  --插入数据之后查看一下表

2.行列转换

2.1 传统方法

通过聚合函数、CASE WHEN ...语句，再搭配集合运算实现行列转换

1）行转列（将SCORE_1转为SCORE_2）

将某一维度的内容拆解成多个新的列：聚合函数+CASE WHEN语句

在oracle中，“case when”用于判断字段多状态的情况，语法为“case 列名 when 条件值1 then 选项1 when 条件值2 then 选项2...else 默认值 end”。

SELECT STUDENT,

               SUM(CASE WHEN COURSE='CHINESE' THEN SCORE ELSE 0 END) CHINESE,
               SUM(CASE WHEN COURSE='MATH' THEN SCORE ELSE 0 END) MATH,
               SUM(CASE WHEN COURSE='ENGLISH' THEN SCORE ELSE 0 END) ENGLISH
               FROM SCORE_1 GROUP BY STUDENT;

聚合函数+decode函数

DECODE语法

DECODE(EXPR,VALUE1,RESULT1,VALUE2,RESULT2,…,DEF_RESULT)

当字段下的数据（EXPR）等于VALUE1时返回RESULT1,等于VALUE2时返回RESULT2,不然返回DEF_RESULT。

代码：

SELECT STUDENT,

               SUM(DECODE(COURSE,'CHINESE',SCORE,0)) CHINESE,
               SUM(DECODE(COURSE,'MATH',SCORE,0)) MATH,
               SUM(DECODE(COURSE,'ENGLISH',SCORE,0)) ENGLISH
               FROM SCORE_1 GROUP BY STUDENT;

2)列转行（将SCORE_2转换成SCORE_1）

将多个列合并成为一个新的维度：单查多列+并集运算（UNION ALL）

SELECT STUDENT,'CHINESE' AS COURSE,CHINESE SCORE FROM SCORE_2
UNION ALL
SELECT STUDENT,'MATH' AS COURSE,MATH SCORE FROM SCORE_2
UNION ALL
SELECT STUDENT,'ENGLISH' AS COURSE,ENGLISH FROM SCORE_2;

之所以使用UNION ALL而不用UNION的原因是因为UNION ALL不会将重复的数据去掉，确保了数据的完整性，而UNION会先去重再合并，可能会导致合并的数据不完整。

2.2 专用函数（PIVOT/UNPIVOT）（都写在FROM后面）

1)行转列 （将SCORE_1转为SCORE_2）    --PIVOT   将维度数据拆解成多个列  --对应  SUM(CASE WHEN ...) 和 SUM(DECODE())

语法：

/*SELECT *

  FROM TB PIVOT(SUM(TB.COL1) FOR TB.COL2 IN (VAL1 AS NEW_COL1,VAL2 AS NEW_COL2,VAL3 AS NEW_COL3));*/

/*-------------------------------------------------------------

                       PIVOT函数注释：

    PIVOT函数内一定要有聚合函数 MAX()

    TB.COL1:被聚合分析的字段  SCORE

    TB.COL2:被拆解的字段  COURSE

    VAL1,VAL2,VAL3...:TB.COL2字段中的某值

    NEW_COL1,NEW_COL2,NEW_COL3...:TB.COL2字段拆解后形成的新字段

    AS NEW_COL部分可以不写，最终会以VAL作为字段名展示

个人理解：

SELECT *

  FROM 表名

PIVOT(MAX(列名)   [要转成的字段下的数据]

   FOR 要转的列 IN('要转的列下的字符名' AS 转成的列名,

               '要转的列下的字符名' AS 转成的列名,

               '要转的列下的字符名' AS 转成的列名,

               '要转的列下的字符名' AS 转成的列名))

 WHERE 1 = 1; --没有条件可以不写

代码展示：

SELECT * FROM SCORE_1 PIVOT(SUM(SCORE) FOR COURSE IN('CHINESE' AS CHINESE,'MATH' AS MATH,'ENGLISH' AS ENGLISH));

2）列转行 （将SCORE_2转为SCORE_1） --UNPIVOT   将多个列合并成一个新的维度 --对应 UNION ALL

语法：

--SELECT *

  --FROM TB PIVOT(NEW_COL1 FOR NEW_COL2 IN (TB.COL1 AS VAL1,TB.COL2 AS VAL2,TB.COL3 AS VAL3));

/*-------------------------------------------------------------

                      UNPIVOT函数注释：

    UNPIVOT函数内不需要聚合函数

    NEW_COL1:用来收纳交叉数据的字段，字段名自取 SCORE

    NEW_COL2:新合成的维度字段，字段名自取   COURSE

    TB.COL1,TB.COL2,TB.COL3...:被并入的字段

    VAL1,VAL2,VAL3...:并入字段以怎样的数据内容录入新维度字段

    AS VAL部分可以不写，最终会以TB.COL作为数据内容录入        

-------------------------------------------------------------*/

代码：

SELECT * FROM SCORE_2 UNPIVOT(SCORE FOR COURSE IN(CHINESE,MATH,ENGLISH));

注意：有的数据库并没有PIVOT和UNPIVOT这两个专用函数，所以我们一般做行列转换用的还是CASE WHEN 和UNION ALL比较多。