Oracle 유용 구분 정리 - PARTITION BY, START WITH...CONNECT BY PRIOR

아래와 같은 임시 테이블(임시VIEW)의 정보를 하나의 ac에 대해서 속해있는 acv 값을 하나의 string로 나열하기.

WITH TVIEW AS (
    SELECT 'a' AS ac , '1a' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '3a' AS acv FROM dual UNION ALL
    SELECT 'e' AS ac , '2e' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '4a' AS acv FROM dual UNION ALL
    SELECT 'b' AS ac , '1b' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '1c' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '2g' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '2c' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '3c' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '2a' AS acv FROM dual UNION ALL       
    SELECT 'd' AS ac , '1d' AS acv FROM dual UNION ALL
    SELECT 'e' AS ac , '1e' AS acv FROM dual UNION ALL   
    SELECT 'f' AS ac , '1f' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '1g' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '3g' AS acv FROM dual UNION ALL
    SELECT 'h' AS ac , '1h' AS acv FROM dual UNION ALL
    SELECT 'h' AS ac , '2h' AS acv FROM dual UNION ALL
    SELECT 'i' AS ac , '1i' AS acv FROM dual
)

예로 다음과 같이 결과를 만들기.

ac  acvl
=== ==============================
a   |1a|2a|3a|4a
b   |1b
c   |1c|2c|3c
...

다음과 같이 쿼리를 작성하면 된다.

SELECT ac ,MAX(SYS_CONNECT_BY_PATH (acv, '|')) AS acvl
FROM (
        SELECT
                ac, acv
                ,(ROW_NUMBER () OVER (PARTITION BY ac  ORDER BY ac, acv  )) RNUM
        FROM TVIEW
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1)
AND PRIOR AC = AC
GROUP BY AC

설명 및 테스트 결과 설명은 다음 글에서 ...