Connect by Level <= n 를 이용한 행복제 방식은
반드시 dual 처럼 1건의 자료만 가지고 수행해야 합니다.
여러건의 자료에 직접 수행하면 원치 않는 기하급수로 늘어나는 결과를 초래하게 됩니다.
 

SELECT DISTINCT
       REGEXP_SUBSTR(val, '[^^]+', 1, lv) val1
  FROM (SELECT t2.val
          FROM table1 t1
             , table1 t2
         WHERE t1.id   = '10002'
           AND t1.name = t2.name
           AND t1.id   = t2.id
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 24)
 WHERE lv <= REGEXP_COUNT(val, '[^^]+')
 ORDER BY val1
;

LISTAGG를 사용해봤습니다.

WITH T (VAL) AS (
SELECT '9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24' FROM DUAL UNION ALL
SELECT '9,10,11,12,13,14,15,16,17,18,19,20,21' FROM DUAL UNION ALL
SELECT '9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24' FROM DUAL UNION ALL
SELECT '14,15,16,17,18,19,20,21,22,23,24' FROM DUAL UNION ALL
SELECT '9,10,11,12,13,14,15,16,17,18,19,20,21,22' FROM DUAL UNION ALL
SELECT '16,17,18,19,20,21,22' FROM DUAL
)
SELECT LISTAGG(V1, ',')
         WITHIN GROUP (ORDER BY CAST (V1 AS INT))
FROM   (SELECT DISTINCT REGEXP_SUBSTR(VAL, '[^,]+', 1, LEVEL) V1
        FROM   (SELECT LISTAGG(VAL, ',')
                         WITHIN GROUP (ORDER BY VAL) VAL
                FROM   T)
CONNECT BY LEVEL <= REGEXP_COUNT(VAL, ',') + 1) 

마농님 답변 너무 감사 드립니다.~