with t as ( select 1 seq, '사과
딸기
바나나' seq_title, '30,000' seq_money from dual union all select 2, '오렌지
자몽', '20,000' from dual ) select seq , ltrim(substr(seq_title, p1, p2-p1), '/') seq_title , seq_money from (select seq , seq_title , seq_money , p1 , lead(p1) over(partition by seq_title order by lv) p2 from (select seq , replace('/'||seq_title||'/', '
', '/') seq_title , seq_money , level lv , instr(replace('/'||seq_title||'/', '
', '/'), '/', 1, level) p1 from t connect by level <= (length(replace('/'||seq_title||'/', '
', '/')) - length(replace(seq_title, '
'))) ) ) where p1 p2 ;
with t as ( select 1 seq, '사과
딸기
바나나' seq_title, '30,000' seq_money from dual union all select 2, '오렌지
자몽', '20,000' from dual ) select seq , ltrim(substr(seq_title, p1, p2-p1), '/') seq_title , seq_money from (select seq , seq_title , seq_money , p1 , lead(p1) over(partition by seq_title order by lv) p2 from (select seq , replace('/'||seq_title||'/', '
', '/') seq_title , seq_money , level lv , instr(replace('/'||seq_title||'/', '
', '/'), '/', 1, level) p1 from t connect by level <= (length(replace('/'||seq_title||'/', '
', '/')) - length(replace(seq_title, '
'))) ) ) where p1 != p2 ;