WITH T ( COL1 , COL2) AS ( SELECT 'A111111','38353965,38359185' FROM DUAL UNION ALL SELECT 'A111112','38361286' FROM DUAL UNION ALL SELECT 'A111113','38351969,81142578' FROM DUAL ), COPY_T AS ( SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= ( SELECT MAX(REGEXP_COUNT(COL2,',')) FROM T ) + 1 ) SELECT COL1 , REGEXP_SUBSTR(COL2,'[^,]+',1,LV) AS VAL FROM T , COPY_T WHERE LV <= REGEXP_COUNT(T.COL2,',') + 1 ORDER BY COL1