단일행을 다중 row로 분리, 다중 row 결과를 단일행으로 연결하는 방법

1. 단일행을 다중 row로 분리

오라클에서 '1,2,3,4,5'라는 문자열을 레코드로 만들려면 어떻게해야 할까?

'1,2,3,4,5'

return
===========
1
2
3
4
5

즉, 결과가 위와같이 나오게 하려면?
connect by를 사용해서 만들수가 있다.

SELECT SUBSTR (str,
                INSTR (str, ',', 1, LEVEL) + 1,
                INSTR (str, ',', 1, LEVEL + 1) - INSTR (str, ',', 1, LEVEL) - 1
                ) sub
  FROM (SELECT ',' || '1,2,3,4,5' || ',' str FROM DUAL)
CONNECT BY LEVEL <= LENGTH (str) - LENGTH (REPLACE (str, ',')) - 1

위와 같이 connect by의 level을 사용해서 구현할 수 있다.
 

2. 다중 row 결과를 단일행으로 연결

이번엔 반대로 해보자.

1
2
3
4
5

return
===========
'1,2,3,4,5'

즉, 이렇게 나오게 하려면 다음과 같이 해보자.

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (str, ',')), 2) path# 
  FROM (
                SELECT str,
                            ROWNUM rnum
                   FROM (
                                SELECT 1 str FROM dual
                                UNION
                                SELECT 2 str FROM dual
                                UNION
                                SELECT 3 str FROM dual
                                UNION
                                SELECT 4 str FROM dual
                                UNION
                                SELECT 5 str FROM dual
                            )
           ) 
START WITH rnum = 1 
CONNECT BY PRIOR rnum = rnum - 1

테이블을 이용해서 같은 결과를 뽑아내려면 다음과 같이 해보자.

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path# 
  FROM (
                SELECT ename, ROWNUM rnum 
                       FROM emp
           ) 
START WITH rnum = 1 
CONNECT BY PRIOR rnum = rnum - 1