2013. 10. 18. 15:36

[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
------------------------------