Oracle一行转多行

1.第一种

SELECT  ID,
       UDF01 AS KEY,
       REGEXP_SUBSTR(UDF01, '[^,]+', 1, LEVEL) AS "First_Str",
       LEVEL AS RN
FROM TEST_TEMP
WHERE ID = '63177'
CONNECT BY LEVEL <= REGEXP_COUNT(UDF01, ',') + 1
AND PRIOR ID= ID
AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY ID, RN

2.第二种

SELECT ID, KEY, RN,
    SUBSTR(STR, INSTR(STR, ',', 1, RN) + 1,
           INSTR(STR, ',', 1, RN + 1) - INSTR(STR, ',', 1, RN) - 1) AS "First_Str"
FROM (
    SELECT  ID, UDF01 AS KEY, ',' || UDF01 || ',' AS STR
    FROM TEST_TEMP WHERE ID = '63177'
) A,
(SELECT ROWNUM AS RN
 FROM DUAL
 CONNECT BY LEVEL <= (SELECT LENGTH(UDF01) - LENGTH(REPLACE(UDF01, ',', '')) + 1
                      FROM TEST_TEMP
                      WHERE ID= '63177')
) B
WHERE INSTR(STR, ',', 1, RN + 1) > 0
ORDER BY A.ID, B.RN