ORACL一行记录拆分为多行

一行记录拆分为多行

STUID	STUNAME	STUSEX
1	aa,bb,cc	1
2	dd,ee	0

方法一

SELECT DISTINCT
    STUID,
    REGEXP_SUBSTR (STUNAME, '[^,]+', 1, LEVEL) STUNAME,
    STUSEX
FROM
    T_STU CONNECT BY LEVEL <= LENGTH (STUNAME) - LENGTH (REPLACE(STUNAME, ',', '')) + 1
ORDER BY
    STUID;

方法二

SELECT
    STUID,
    REGEXP_SUBSTR (STUNAME, '[^,]+', 1, lv) STUNAME,
    STUSEX
FROM
    T_STU,
    (
        SELECT
            LEVEL lv
        FROM
            dual CONNECT BY LEVEL < 10
    ) b
WHERE
    b.lv <= REGEXP_COUNT (T_STU.STUNAME, '\,') + 1
ORDER BY
    STUID;

方法三

SELECT STUID,
       SUBSTR(A.STUNAME,
              INSTR(A.STUNAME,
                    ',',
                    1,
                    LEVELS.LVL) + 1,
              INSTR(A.STUNAME,
                    ',',
                    1,
                    LEVELS.LVL + 1) -
              (INSTR(A.STUNAME,
                     ',',
                     1,
                     LEVELS.LVL) + 1)) AS STUNAME,
       STUSEX
FROM   (SELECT STUID,
               ',' || STUNAME || ',' AS STUNAME,
               STUSEX,
               LENGTH(STUNAME) - NVL(LENGTH(REPLACE(STUNAME,
                                                    ',')),
                                     0) + 1 AS CNT --取待拆分字段每行按照分隔符','分割后的记录数，用于connect by
        FROM   T_STU) A,
       (SELECT ROWNUM AS LVL --产生一个待拆分字段分割后最大记录数的序列
        FROM   (SELECT MAX(LENGTH(STUNAME || ',') -
                           NVL(LENGTH(REPLACE(STUNAME,
                                              ',')),
                               0)) MAX_LEN
                FROM   T_STU)
        CONNECT BY LEVEL <= MAX_LEN) LEVELS
WHERE  LEVELS.LVL <= A.CNT --笛卡尔连接
ORDER  BY STUID;