【DB笔试面试467】Oracle中行列互换有哪些方法？

本文主要是介绍【DB笔试面试467】Oracle中行列互换有哪些方法？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Oracle中行列互换有哪些方法？

行列转换包括以下六种情况：（1）列转行。（2）行转列。（3）多列转换成字符串。（4）多行转换成字符串。（5）字符串转换成多列。（6）字符串转换成多行。其中，重点是行转列和字符串转换成多行。

下面将分别对这几种情况举例来说明。

1、列转行

列转行就是将原表中的列名作为转换后的表的内容。列转行主要采用UNION ALL来完成。示例代码如下所示：

CREATE TABLE TEST_LHR

(

  NAME     VARCHAR2(255),

  JANUARY  NUMBER(18),

  FEBRUARY NUMBER(18),

  MARCH    NUMBER(18),

  APRIL    NUMBER(18),

  MAY      NUMBER(18)

);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('长寿', 58, 12, 26, 18, 269);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('璧山', 33, 18, 17, 16, 206);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('杨家坪', 72, 73, 79, 386, 327);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('巫溪', 34, 9, 7, 21, 33);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('丰都', 62, 46, 39, 36, 91);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('武隆', 136, 86, 44, 52, 142);

COMMIT;

SELECT * FROM TEST_LHR;

下面进行列转换：

SELECT *

  FROM (SELECT T.NAME, 'JANUARY' MONTH, T.JANUARY V_NUM

          FROM TEST_LHR T

        UNION ALL

        SELECT T.NAME, 'FEBRUARY' MONTH, T.FEBRUARY V_NUM

          FROM TEST_LHR T

        UNION ALL

        SELECT T.NAME, 'MARCH' MONTH, T.MARCH V_NUM

          FROM TEST_LHR T

        UNION ALL

        SELECT T.NAME, 'APRIL' MONTH, T.APRIL V_NUM

          FROM TEST_LHR T

        UNION ALL

        SELECT T.NAME, 'MAY' MONTH, T.MAY V_NUM

          FROM TEST_LHR T)

 ORDER BY NAME;

2、行转列

行转列就是将行数据内容作为列名。示例代码如下所示：

CREATE TABLE T_ROW_COL_LHR(

NUM VARCHAR2(15 CHAR),

NAME VARCHAR2(20 CHAR),

SEX VARCHAR2(2 CHAR),

CLASSES VARCHAR2(30 CHAR),

COURSE_NAME VARCHAR2(50 CHAR)

);

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME)  VALUES ('206211','王艺','男','06-1班','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务管理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务会计');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','电子商务');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公共经济学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公司理财');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','管理学原理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务管理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务会计');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','电子商务');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','公共经济学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','环境管理学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','管理学原理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','商务谈判');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206216','李佳琪','男','06-2','土地估计');

SELECT * FROM T_ROW_COL_LHR;

将COURSE_NAME进行行转列：

SELECT NUM,NAME,SEX,CLASSES,

            MAX(DECODE(RN,1,COURSE_NAME,NULL)) COURSE_NAME_1,

            MAX(DECODE(RN,2,COURSE_NAME,NULL)) COURSE_NAME_2,

            MAX(DECODE(RN,3,COURSE_NAME,NULL)) COURSE_NAME_3,

            MAX(DECODE(RN,4,COURSE_NAME,NULL)) COURSE_NAME_4,

            MAX(DECODE(RN,5,COURSE_NAME,NULL)) COURSE_NAME_5

FROM   (SELECT NUM,NAME,SEX,CLASSES,COURSE_NAME,

ROW_NUMBER() OVER(PARTITION BY NUM,NAME,SEX,CLASSES ORDER BY COURSE_NAME) RN

                   FROM   T_ROW_COL_LHR)

GROUP  BY NUM,NAME,SEX,CLASSES;

结果如下所示：

将COURSE_NAME列合并，示例代码如下所示：

SELECT NUM,

       NAME,

       SEX,

       CLASSES,

       (MAX(DECODE(RN, 1, COURSE_NAME, NULL)) ||

       MAX(DECODE(RN, 2, ',' || COURSE_NAME, NULL)) ||

       MAX(DECODE(RN, 3, ',' || COURSE_NAME, NULL)) ||

       MAX(DECODE(RN, 4, ',' || COURSE_NAME, NULL)) ||

       MAX(DECODE(RN, 5, ',' || COURSE_NAME, NULL))) NAME

FROM   (SELECT NUM,

               NAME,

               SEX,

               CLASSES,

               COURSE_NAME,

               ROW_NUMBER() OVER(PARTITION BY NUM, NAME, SEX, CLASSES ORDER BY COURSE_NAME) RN

        FROM   T_ROW_COL_LHR)

GROUP  BY NUM,

          NAME,

          SEX,

          CLASSES;

结果如下所示：

3、多列转换成字符串

使用||或CONCAT函数实现。示例代码如下所示。

SELECT CONCAT('A','B') FROM DUAL;

4、多行转换成字符串

示例代码如下所示：

CREATE TABLE T_ROW_STR(

ID INT,

COL VARCHAR2(10)

);

INSERT INTO T_ROW_STR VALUES(1,'A');

INSERT INTO T_ROW_STR VALUES(1,'B');

INSERT INTO T_ROW_STR VALUES(1,'C');

INSERT INTO T_ROW_STR VALUES(2,'A');

INSERT INTO T_ROW_STR VALUES(2,'D');

INSERT INTO T_ROW_STR VALUES(2,'E');

INSERT INTO T_ROW_STR VALUES(3,'C');

COMMIT;

SELECT * FROM T_ROW_STR;

SELECT ID,

       MAX(DECODE(RN, 1, COL, NULL)) ||

       MAX(DECODE(RN, 2, ',' || COL, NULL)) ||

       MAX(DECODE(RN, 3, ',' || COL, NULL)) STR

FROM   (SELECT ID,

               COL,

               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN

        FROM   T_ROW_STR) T

GROUP  BY ID

ORDER  BY 1;

也可以使用SYS_CONNECT_BY_PATH来实现：

SELECT T.ID ID,

       MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.COL, ','), 2)) STR

FROM   (SELECT ID,

               COL,

               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) RN

        FROM   T_ROW_STR) T

START  WITH RN = 1

CONNECT BY RN = PRIOR RN + 1

    AND    ID = PRIOR ID

GROUP  BY T.ID;

5、字符串转换成多列

实际上就是一个字符串拆分的问题。示例代码如下所示：

CREATE TABLE T_COL_ROW(

ID INT,

C1 VARCHAR2(10),

C2 VARCHAR2(10),

C3 VARCHAR2(10));

INSERT INTO T_COL_ROW VALUES (1, 'v11', 'v21', 'v31');

INSERT INTO T_COL_ROW VALUES (2, 'v12', 'v22', NULL);

INSERT INTO T_COL_ROW VALUES (3, 'v13', NULL, 'v33');

INSERT INTO T_COL_ROW VALUES (4, NULL, 'v24', 'v34');

INSERT INTO T_COL_ROW VALUES (5, 'v15', NULL, NULL);

INSERT INTO T_COL_ROW VALUES (6, NULL, NULL, 'v35');

INSERT INTO T_COL_ROW VALUES (7, NULL, NULL, NULL);

COMMIT;

SELECT * FROM T_COL_ROW;

CREATE TABLE T_STR_COL AS

SELECT ID,C1||','||C2||','||C3 AS C123

FROM T_COL_ROW;

SELECT * FROM T_STR_COL;

SELECT ID,

       C123,

       SUBSTR(C123, 1, INSTR(C123 || ',', ',', 1, 1) - 1) C1,

       SUBSTR(C123,

              INSTR(C123 || ',', ',', 1, 1) + 1,

              INSTR(C123 || ',', ',', 1, 2) - INSTR(C123 || ',', ',', 1, 1) - 1) C2,

       SUBSTR(C123,

              INSTR(C123 || ',', ',', 1, 2) + 1,

              INSTR(C123 || ',', ',', 1, 3) - INSTR(C123 || ',', ',', 1, 2) - 1) C3

FROM   T_STR_COL

ORDER  BY 1;

6、字符串转换成多行

示例代码如下所示：

CREATE TABLE T_STR_ROW AS

SELECT ID,

       MAX(DECODE(RN, 1, COL, NULL)) ||

              MAX(DECODE(RN, 2, ',' || COL, NULL)) ||

              MAX(DECODE(RN, 3, ',' || COL, NULL)) STR

  FROM (SELECT ID,

               COL,

               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN

          FROM T_ROW_STR) T

  GROUP BY ID

  ORDER BY 1;

SELECT * FROM T_STR_ROW;

SELECT ID,

       1 AS P,

       SUBSTR(STR, 1, INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM   T_STR_ROW

UNION ALL

SELECT ID,

       2 AS P,

       SUBSTR(STR,

              INSTR(STR || ',', ',', 1, 1) + 1,

              INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM   T_STR_ROW

UNION ALL

SELECT ID,

       3 AS P,

       SUBSTR(STR,

              INSTR(STR || ',', ',', 1, 1) + 1,

              INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM   T_STR_ROW

ORDER  BY 1,

          2;

还有几类特殊的转换，如下所示：

CREATE OR REPLACE TYPE INS_SEQ_TYPE IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(INS_SEQ_TYPE(1, 2, 3, 4, 5));

结果：

COLUMN_VALUE

------------

           1

           2

           3

           4

           5

若是字符串类型，则如下所示：

CREATE OR REPLACE TYPE INS_SEQ_TYPE2 IS VARRAY(80) OF VARCHAR2(32767);

SELECT * FROM TABLE(INS_SEQ_TYPE2('aadf,dea','cbc','d'));

结果：

COLUMN_VALUE

-----------------

aadf,dea

cbc

d

还有如下的形式：

先创建一个TYPE类型，代码如下：

CREATE OR REPLACE TYPE TYPE_STR_LHR IS TABLE OF VARCHAR2(32767);

再创建FUN_SPLIT2_LHR函数，代码如下：

CREATE OR REPLACE FUNCTION FUN_SPLIT2_LHR(P_STR       VARCHAR2,

                                 V_SPLIT VARCHAR2 DEFAULT ',') RETURN TYPE_STR_LHR IS

  RS    TYPE_STR_LHR := TYPE_STR_LHR();

  V_STR VARCHAR2(4000) := '';

  V_LEN NUMBER := 0;

BEGIN

  V_STR := P_STR;

  V_LEN := LENGTH(V_SPLIT);

  WHILE LENGTH(V_STR) > 0 LOOP

    IF INSTR(V_STR, V_SPLIT) > 0 THEN

      RS.EXTEND;

      RS(RS.COUNT) := SUBSTR(V_STR, 1, INSTR(V_STR, V_SPLIT) - 1);

      V_STR := SUBSTR(V_STR, INSTR(V_STR, V_SPLIT) + V_LEN);

    ELSE

      RS.EXTEND;

      RS(RS.COUNT) := V_STR;

      EXIT;

    END IF;

  END LOOP;

  RETURN RS;

END;

测试如下：

SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101,102,103',','));

COLUMN_VALUE

------------------

101

102

103

SQL> SELECT TO_NUMBER(COLUMN_VALUE) FROM TABLE(FUN_SPLIT2_LHR('101,102,103'));

TO_NUMBER(COLUMN_VALUE)

-----------------------

                   101

                   102

                   103

SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101@#102@#103','@#'));

COLUMN_VALUE

---------------

101

102

103

& 说明：

有关行列互换更多的案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1272538/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

---------------优质麦课------------

 详细内容可以添加麦老师微信或QQ私聊。

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

这篇关于【DB笔试面试467】Oracle中行列互换有哪些方法？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！