나의 향기에 젖다...

## 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 /오빠

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

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