ORALCE 单行转多行

 

WITH TESTTABLE AS (
    SELECT 'TEST1' GROUPNAME , 'A,B,C,D,E' VALUENAME FROM DUAL
    UNION ALL
    SELECT 'TEST2' GROUPNAME , 'A,B,C,D' VALUENAME FROM DUAL
)

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

————————————————

原文链接：https://blog.csdn.net/zy123698745/article/details/83538139