[Oracle] SYS_CONNECT_BY_PATH 문자열 합치기??

## SAMPLE 1

 

 SELECT SUBSTRB(MAX(SYS_CONNECT_BY_PATH(CARTONID,', ')),2) CARTONID
   FROM (
         SELECT ROWNUM RW, NVL(B.CARTONID,C.CARTONID) CARTONID
           FROM TTASKINHI A, TCONTENT B, TCONTENTHI C
          WHERE A.OWNERID = '1474'
            AND A.ORDERID = '131001-901642'
            AND A.ITEMID  = '1118825'
            AND A.CONTENTID = B.CONTENTID(+)
            AND A.CONTENTID = C.CONTENTID(+)
        )
  START WITH RW=1
CONNECT BY PRIOR RW=RW-1;

 

 

1. 내부 쿼리 실행시

RW CARTONID

1    32021092K
2    32021092K
3    32021092K
4    32021092K

 

2. 전체 쿼리 실행시

CARTONID

32021092K, 32021092K, 32021092K, 32021092K

 

 

## SAMPLE 2

 

 SELECT A, MAX(SYS_CONNECT_BY_PATH(B,'/')) B
    FROM (
                SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
                           , A, B
                   FROM (
                                SELECT 1 A, '엄마' B FROM DUAL
                                UNION
                                SELECT 1 A, '아빠' B FROM DUAL
                                UNION
                                SELECT 1 A, '이모' B FROM DUAL
                                UNION
                                SELECT 2 A, '삼촌' B FROM DUAL
                                UNION
                                SELECT 2 A, '오빠' B FROM DUAL
                             )
              )
 START WITH RNUM = 1
 CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A=A
 GROUP BY A

 

 

1. 내부 쿼리 실행시

A B

1 아빠
1 엄마
1 이모
2 삼촌
2 오빠

 

2. 전체 쿼리 실행시

A B

1 /아빠/엄마/이모
2 /삼촌/오빠

 

3. RNUM = 1 -> RNUM = 2 변경시

A B

1 /엄마/이모
2 /오빠

 

 

---------------------------------------------------------------------------------------------

 

 

### 타블로그 참조 ###

 

 

SELECT DEPTNO, ENAME FROM EMP; 했을 때 결과

------------------------------

DEPTNO | ENAME

------------------------------

1 | A1

1 | A2

2 | B1

2 | B2

2 | B3

3 | C1

3 | C2

3 | C3

3 | C4

4 | D1

4 | D2

------------------------------

 

##

 

 

ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY ENAME ) RNUM

: DEPTNO를 기준으로 순서대로 번호를 준다. 예시를 보는 것이 빠르겠다..

SELECT

DEPTNO

,ENAME

,ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY ENAME ) RNUM

FROM

EMP;

------------------------------

DEPTNO | ENAME | RNUM

------------------------------

1 | A1 | 1

1 | A2 | 2

2 | B1 | 1

2 | B2 | 2

2 | B3 | 3

3 | C1 | 1

3 | C2 | 2

3 | C3 | 3

3 | C4 | 4

4 | D1 | 1

4 | D2 | 2

------------------------------

DEPTNO 들끼리 묶은 후 순서대로 ROW 하나당 1씩 증가한다.

 

##

SYS_CONNECT_BY_PATH .................. CONNECT BY

그냥 직접 돌려 보는 게 가장 빠르다....

 

##

SELECT

DEPTNO

,SUBSTR(MAX( SYS_CONNECT_BY_PATH(ENAME, ',')), 2) PATH

FROM

(

SELECT

ENAME

,DEPTNO

,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) RNUM

FROM

EMP

)

START WITH RNUM = 1

CONNECT BY

PRIOR RNUM = RNUM - 1

AND PRIOR DEPTNO = DEPTNO

GROUP BY DEPTNO

결과

------------------------------

DEPTNO | PATH

------------------------------

1 | A1,A2

2 | B1,B2,B3

3 | C1,C2,C3,C4

4 | D1,D2

------------------------------

[출처] SYS_CONNECT_BY_PATH connect_by_path (COL을 ROW로)|작성자 정서