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
------------------------------
Trackback 0 Comment 0
2011. 8. 30. 15:36

문자열 결합 그룹핑

아래 처럼 두개의 테이블이 있고

TB_A
 ------      -------     ------
 A_ID        B_ID        A_DATA
 ------      -------     ------
 1           1           가가
 2           1           나나
 3           1           다다
 4           2           거거
 5           2           너너
 ....
 

TB_B
 -------    ------
 B_ID       B_DATA 
 -------    ------ 
 1          123 
 2          456 
 3          789 
 4          123 
 5          111


여기서 TB_B 의 B_ID = 1인 데이타를 조회 한 결과...
 
 -------    ------     -------
 B_ID       B_DATA     A_DATA
 -------    ------     -------
 1          123        가가, 나나, 다다   <--- 이렇게 한줄로...


[MSSQL]
  
 SELECT B_ID, B_DATA,
              STUFF( (SELECT  ',' + A_DATA 
                              FROM  TB_A A WHERE A.B_ID = B.B_ID 
                           FOR XML PATH('')),1,1,'')
    FROM TB_B B
  WHERE B_ID = 1;
  
 
[ORACLE]
 
 SELECT B_ID,
              SUBSTR(MAX(sys_connect_By_pAth(A_DATA,',')),2)
    FROM (
                SELECT A.B_ID, B_DATA,A_DATA,
                             ROW_NUMBER() OVER(PARTITION BY A.B_ID ORDER BY A.A_ID) rnum
                   FROM TB_A A, TB_B B
                 WHERE A.B_ID = B.B_ID
              )
   WHERE B_ID = 1
    START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum-1
       AND PRIOR B_ID = B_ID
   GROUP BY B_ID;


'Database / Sql' 카테고리의 다른 글

[Oracle] FLASHBACK  (0) 2011.09.02
[Oracle] BIN$... 으로 시작하는 테이블  (0) 2011.08.31
문자열 결합 그룹핑  (0) 2011.08.30
[Oracle] JOIN UPDATE  (0) 2011.05.25
[Oracle] JOIN UPDATE (bypass_ujvc 힌트)  (0) 2011.05.25
[MSSQL] WHERE 절에 CASE문 추가  (0) 2011.03.21
Trackback 0 Comment 0