-- Sort 기준이 없기에 rownum 으로 임의의 sort 기준을 적용하였습니다. with t as ( select 'ICN/DXB/OSL' route, 100 a, 200 b from dual union all select 'ICN/BKK/SGN', 200, 300 from dual union all select 'ICN/NVI/AMS', 300, 400 from dual ) select regexp_substr(route, '[^/]+', 1, lv+1) route , a , b from (select t.*, rownum as sort from t) , (select level lv from dual connect by level <=2) order by sort ;
with t as ( select 'ICN/DXB/OSL' x, 100 y, 200 z from dual union all select 'ICN/BKK/SGN', 200, 300 from dual union all select 'ICN/NVI/AMS', 300, 400 from dual ) select x,y,z , regexp_substr(x, '[^/]+', 1, b.lv + 1) val from t a, (select level lv from dual connect by level <=99) b where b.lv <= regexp_count(x, '/') order by x, lv