다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (1) [펌]

안녕하세요. 오늘부터 김홍선님의 'SQL Query Tips' 게시판에 올라와있는 여러
유용한 내용들에 대한 풀이를 올리게 된 엑셥이라고 합니다. 

김홍선님의 허락을 득하고 올리게 되었으며, 이런 기회를 주신 김홍선님에게 감사의
말씀을 올립니다. ^^;

시간 날때마가 김홍선님의 좋은 내용들을 알기쉽게 풀이하여 올리도록 하겠으며 
혹시라도 이해가 잘 안가는 부분이 있다면 리플을 달아주시면 고맙겠습니다.

/*************************************************************************************/
/* 다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법                                  */
/*************************************************************************************/
-- 1. 원본 데이터 만들기
SELECT *
FROM   (SELECT '홍길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '김길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '이길동' RETURN#
        FROM   DUAL) TAB
;

-- 2. 여러 ROW로 되어있는 레코드들을 하나씩 나눠서 가져오기
이에 대한 문제를 해결하기 위해서는 각각의 원하는 레코드를 추출하여 이를 하나의 레코드상에
나타내야 합니다. 그러나 집합적 사고가 아닌 절차적 사고에 익숙해져 있는 개발자라면 이 문제에 
대해서 원하는 레코드들을 하나씩 변수에 담고 이를 하나로 묶는 생각을 가지게 될 것입니다. 
즉 아래와 같이 

DECLARE
    VAL_1   VARCHAR2(30) := NULL;
    VAL_2   VARCHAR2(30) := NULL;
    VAL_3   VARCHAR2(30) := NULL;
    
    VAL_ALL VARCHAR2(100) := NULL;
BEGIN
    -- 홍길동 데이터 추출 
    BEGIN
        SELECT RETURN#
        INTO   VAL_1
        FROM   TAB
        WHERE  RETURN# = '홍길동';
    EXCEPTION 
        WHEN OTHERS THEN
            VAL_1 = NULL;
    END;
        
    -- 김길동 데이터 추출 
    BEGIN
        SELECT RETURN#
        INTO   VAL_2
        FROM   TAB
        WHERE  RETURN# = '김길동';
    EXCEPTION 
        WHEN OTHERS THEN
            VAL_2 = NULL;
    END;
    
    -- 이길동 데이터 추출 
    BEGIN
        SELECT RETURN#
        INTO   VAL_2
        FROM   TAB
        WHERE  RETURN# = '이길동';
    EXCEPTION 
        WHEN OTHERS THEN
            VAL_3 = NULL;
    END;
    
    -- 추출한 데이터를 하나의 변수에 넣음
    VAL_4 = VAL_1 || ', ' || VAL_2 || ', ' || VAL_3;    
END
;    

접근을 할텐데요. 이렇게 된다면 한번만 DB에 갔다와서 해결할 수 있는 문제를 총 3번을 DB에 갔다와서
데이터를 가져오게 되므로 DB I/O에 부하를 주게됩니다. 여기서 3번만 갔다오는것과 한번만 갔다오는것이
얼마나 차이가 나겠냐고 생각하시는 분들도 계시겠지만, 만약 100만개의 레코드가 있는 테이블에서
이와같은 결과를 도출해내야 한다고 생각하시면 충분히 공감하실겁니다. 물론 100만개의 레코드면 
Copy & Paste도 무진장 해야겠지요? ^^;

그래서 절차적 사고로 DB의 데이터를 원하는 결과로 추출하기에는 상당히 제한이 많이 있습니다.
물론 모든 결과에 대해 SQL로 표현할수는 없고, 이에 대해서는 PL/SQL로 접근해야 하는 경우도 있죠.
하지만 충분히 SQL로 할 수 있는 부분들을 PL/SQL로 해서 DB I/O에 부하를 주게 된다면 DB를 단순히
읽기(Read)만하는 툴로써 사용할 수 없을 것입니다. 즉, DB 개발자라면 절차적사고를 버리고 집합적 
사고로 접근을 해야한다는 것이죠. 앞으로 이 집합적 사고에 대해서는 지속적으로 말씀을 드리겠습니다.

그러면 이제 집합적 사고로 접근을 한번 해보겠습니다.

우리가 아는 오라클 명령어 중 DECODE()라는 함수가 있습니다. 즉 'IF ELSE END' 구문을 SQL명령어로
나타낸 구문이죠. 이 구문을 이용하여 현재 3개의 로우로 되어있는 것을 따로따로 떼어내 보겠습니다.
;

SELECT RETURN#
     , DECODE(RETURN#,
              '홍길동',
              RETURN#) "NAME_1"
     , DECODE(RETURN#,
              '김길동',
              RETURN#) "NAME_2"
     , DECODE(RETURN#,
              '이길동',
              RETURN#) "NAME_3"             
FROM   (SELECT '홍길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '김길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '이길동' RETURN#
        FROM   DUAL) TAB
;

RETURN#	NAME_1	NAME_2	NAME_3
홍길동	홍길동
김길동		김길동
이길동			이길동

자, 이제 우리는 3개의 로우로 되어있는 것들을 각각 하나씩 따로 분리하였습니다.
그런데 분리는 했지만 아직까지 3개의 로우인것은 변함이 없군요. 이제 이 3개의 
로우를 하나의 로우로 만들기 위해 집합적인 사고를 가져보도록 하겠습니다.
;

우리가 알고 있는 집합함수중에 자주 쓰는 함수들이 있지요. MIN(), MAX(), SUM(), AVG() 등등.
이 집합함수는 어떤 값을 기준으로 그 값을 가지는 것들을 집합으로 묶어서 그 값들을 집합함수를
통해 가공하여 표현을 합니다. 우리는 이 집합함수를 이용하여 하나의 레코드로 표현을 해보겠습니다.
;

구분	①	②	③
RETURN#	NAME_1	NAME_2	NAME_3
홍길동	홍길동
김길동		김길동
이길동			이길동

위에 제가 표시한 내용을 보시면 ①, ②, ③ 이 있는데 이 가공된 컬럼에서 값이 있는 것은 딱 하나밖에 없지요.
이 얘기는 다른 말로 표시하면 전체 그룹(따로 GROUP BY를 하지 않았을 때)에서 보면 ①, ②, ③ 각각의 필드에서
값에 대해 MIN() 또는 MAX()함수를 이용하면 값이 있는것만 가져올 수 있습니다. 이유는 집합함수는 NULL 값에 
대해 연산을 하지 않기 떄문이죠. 그래서 아래에 이에 대한 내용을 첨가하여 다시 쿼리를 해보겠습니다.
하지만 우리는 우리가 집합으로 만든 내용에 대해 다시 가공을 하는 것이므로 인라인 뷰로 내려서 가져와야 합니다.
;

-- 인라인 뷰로 내려서 가져오면 우리가 1차 가공한 내용들이 하나의 집합이 되었습니다.
SELECT *
FROM   (SELECT RETURN#
             , DECODE(RETURN#,
                      '홍길동',
                      RETURN#) "NAME_1"
             , DECODE(RETURN#,
                      '김길동',
                      RETURN#) "NAME_2"
             , DECODE(RETURN#,
                      '이길동',
                      RETURN#) "NAME_3"             
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL) TAB)
;

-- 이 집합을 MIN()함수를 써서 가공해 보겠고, RETURN# 필드는 이제 필요가 없으므로 따로 표시하진 않습니다.
SELECT MIN(NAME_1) 
     , MIN(NAME_2) 
     , MIN(NAME_3) 
FROM   (SELECT RETURN#
             , DECODE(RETURN#,
                      '홍길동',
                      RETURN#) "NAME_1"
             , DECODE(RETURN#,
                      '김길동',
                      RETURN#) "NAME_2"
             , DECODE(RETURN#,
                      '이길동',
                      RETURN#) "NAME_3"             
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL) TAB)
;                                

자, 이제 우리가 원하는 로우로 되어있는 값들을 하나의 레코드로 표현하였습니다.
하지만 최종으로 표현하는 결과와는 차이가 있지요? 최종결과는 한 필드로 콤마로 구문하여 표현하는
것이니까요. 그래서 우리는 이렇게 나온 필드를 하나로 합쳐보겠습니다.
;

SELECT MIN(NAME_1) || ', ' ||
       MIN(NAME_2) || ', ' ||
       MIN(NAME_3) 
FROM   (SELECT RETURN#
             , DECODE(RETURN#,
                      '홍길동',
                      RETURN#) "NAME_1"
             , DECODE(RETURN#,
                      '김길동',
                      RETURN#) "NAME_2"
             , DECODE(RETURN#,
                      '이길동',
                      RETURN#) "NAME_3"             
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL) TAB)
;      

이제 우리가 원하는 최종결과가 나왔습니다. 이 내용은 김홍선님이 기술한 내용인 'column-to-row pivot'을 
사용하였습니다. 하지만 이에 대한 결과가 나오기 위해 하나의 레코드를 일일이 나누었기 때문에 만약 100만
레코드에 대해 이와 같이 표현하여야 한다면 이 또한 Copy & Paste를 벗어날 수 없겠지요.

그래서 김홍선님이 제시하신 CONNECT BY를 사용해 보도록 하겠습니다. 

CONNECT BY 함수는 하나의 테이블에서 계층으로 되어있는 데이터를 추출하기 위해 제공된 함수이며 계층쿼리 함수로 불립니다.
Oracle 9i부터 제공되었으며, 이 함수를 통해 이전에는 PL/SQL로 해야 했던 작업들을 한방에 해결하게 되었습니다.

일단 이 함수의 사용 용도부터 알아봐야겠죠? 다른 곳에서는 EMP 테이블의 EMPNO와 MGR필드를 가지고 예를 들었는데
저는 실제 ERP 생산모듈에서 사용되는 BOM(Bill OF Materials)으로 간략히 설명해 보겠습니다.

BOM은 자재명세서라고 불리고 있는데요. 테이블을 예로 들어 보겠습니다. 공장에서 테이블을 만들기 위해서는 무엇이 필요할까요?
일단 큰 네모난 판자(?)가 필요하겠죠? 그리고 그 판자를 받칠 수 있는 기둥이 필요하겠구요. 이제 기본적으로 필요한 
자재들이 나왔습니다. 

테이블 1개 = (판자 1개) + (기둥 4개) 

그런데 이것만 가지고는 테이블을 완성할수는 없겠죠? 기둥을 판자에 고정시키기 위해 각 기둥마다 볼트를 고정시키기 위한 
고정판 1개와 볼트가 4개가 필요할 것입니다. 또한 이 테이블은 판자만 있는것이 아니라 책꽃이까지 만들어서 상품화를 시키는 제품입니다.
이때는 각 판자 한개당 책꽃이가 하나씩 필요하겠죠? 그럼 여기까지 나온 자재를 다시 나열해 보겠습니다.

테이블 1개 = (판자 1개)   + (기둥 4개)   
판자 1개   = (책꽃이 1개)
기둥 1개   = (고정판 1개) + (볼트 4개)

이제 이 개념을 LEVEL로 잡아보겠습니다.
;

아이템	수량	상위품목
테이블	1
판자	1	테이블
기둥	4	테이블
책꽃이	1	판자
고정판	1	기둥
볼트	4	기둥

위에 표현한 내용을 보면 테이블 하나를 만들기 위해 5개의 아이템이 필요하다는것을 알 수 있습니다. 그리고 상위품목을 통해서 현재
아이템의 레벨을 알 수도 있습니다.

LEVEL	아이템	수량	상위품목
1	테이블	1
2	판자	1	테이블
2	기둥	4	테이블
3	책꽃이	1	판자
3	고정판	1	기둥
3	볼트	4	기둥

이처럼 레벨로 보이도록 하는 함수가 이 CONNECT BY 입니다.여기서는 간략히 설명하기 위해 이정도로 표현을 했지만 실제에서 사용하는 
BOM은 어마어마한 깊이의 레벨을 가지고 있는 BOM도 있습니다. 노트북이나 자동차를 떠올리면 쉽겠지요?

이제 이와 같은 데이터가 있는 테이블을 쿼리에서 CONNECT BY를 통해 레벨을 표현해 보겠습니다.
;

-- 원본 데이터를 일단 만들어 보겠습니다.
SELECT '테이블' ITEM
     , 1        QTY
     , NULL     PARENT_ITEM
FROM   DUAL
UNION ALL
SELECT '판자',   1, '테이블' FROM DUAL 
UNION ALL
SELECT '기등',   4, '테이블' FROM DUAL 
UNION ALL
SELECT '책꽃이', 1, '판자'   FROM DUAL 
UNION ALL
SELECT '고정판', 1, '기둥'   FROM DUAL 
UNION ALL
SELECT '볼트',   4, '기둥'   FROM DUAL 
;

-- 이제 CONNECT BY를 사용하여 레벨을 표시해 보겠습니다.
SELECT LEVEL
     , ITEM
     , QTY
     , PARENT_ITEM
FROM   (SELECT '테이블' ITEM
             , 1        QTY
             , NULL     PARENT_ITEM
        FROM   DUAL
        UNION ALL
        SELECT '판자',   1, '테이블' FROM DUAL 
        UNION ALL
        SELECT '기둥',   4, '테이블' FROM DUAL 
        UNION ALL
        SELECT '책꽃이', 1, '판자'   FROM DUAL 
        UNION ALL
        SELECT '고정판', 1, '기둥'   FROM DUAL 
        UNION ALL
        SELECT '볼트',   4, '기둥'   FROM DUAL)
START WITH ITEM = '테이블'           -- ①
CONNECT BY PRIOR ITEM = PARENT_ITEM  -- ②
;

이렇게 계층으로 구성되어 있는 테이블의 데이터를 보기위해 CONNECT BY를 사용하고 있습니다.
CONNECT BY 순서는 START WITH, CONNECT BY, WHERE 순서로 진행하고 있습니다.

이제 CONNECT BY 사용법도 알았으니 우리가 풀어야했던 내용을 살펴보겠습니다.
;

-- 원본 데이터
SELECT *
FROM   (SELECT '홍길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '김길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '이길동' RETURN#
        FROM   DUAL) TAB
;

-- 이 데이터를 가지고 레벨 개념을 넣어야 하므로 ROWNUM으로 레벨 개념을 넣겠습니다.
SELECT ROWNUM CNT
     , RETURN#
FROM   (SELECT '홍길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '김길동' RETURN#
        FROM   DUAL
        UNION ALL
        SELECT '이길동' RETURN#
        FROM   DUAL)
;

여기서 ROWNUM 2는 1 + 1 이므로 역으로 말하면 1 = 2 - 1 즉, ROWNUM = ROWNUM - 1 이 됩니다.
이 내용을 넣어보도록 하겠습니다(여기서 ROWNUM도 하나의 필드로 만들어야 하므로 인라인 뷰로 넣겠습니다)
;

SELECT LEVEL
     , CNT
     , RETURN#
FROM   (SELECT ROWNUM CNT
             , RETURN#
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL))
START WITH RETURN# = '홍길동'
CONNECT BY PRIOR CNT = CNT - 1                
;

드디어 레벨이 나왔습니다. 레벨이 나왔다는것은 CONNECT BY로 계층이 풀렸다는 얘기입니다.
또한 이 얘기는 CONNECT BY에서 제공되는 함수를 사용할 수 있다는 얘기이지요.

여기서 김홍선님이 사용하신 'SYS_CONNECT_BY_PATH' 함수는 최초 ROOT의 내용을 가지고 LEAF까지 보여주는 함수입니다.
9i에서 제공하고, 10g에서 제공하는 함수는 'CONNECT_BY_ROOT', 'CONNECT_BY_ISLEAF' 등이 있습니다.

이제 'SYS_CONNECT_BY_PATH'를 사용하여 보겠습니다.
;

SELECT SYS_CONNECT_BY_PATH(RETURN#, ',')
FROM   (SELECT ROWNUM CNT
             , RETURN#
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL))
START WITH RETURN# = '홍길동'
CONNECT BY PRIOR CNT = CNT - 1  
;

,홍길동

,홍길동,김길동

,홍길동,김길동,이길동

그런데 총 3개의 레코드가 나왔군요. 우리가 원하는 레코드는 가장 긴 레코드인데요. 그래서 3개의 레코드 중 가장 길이가 긴 놈을 추출하기 위해
MAX()함수를 사용합니다.
;

SELECT MAX(SYS_CONNECT_BY_PATH(RETURN#, ','))
FROM   (SELECT ROWNUM CNT
             , RETURN#
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL))
START WITH RETURN# = '홍길동'
CONNECT BY PRIOR CNT = CNT - 1  
;

이제 우리가 원하는 결과와 거의 근접을 했습니다. 하지만 'SYS_CONNECT_BY_PATH' 함수를 사용할 때 구분자로 사용되었던 콤마가 맨앞에도 보입니다.
그래서 우리는 SUBSTR()을 이용해서 2번째부터 출력을 하겠습니다.
;

SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(RETURN#, ',')), 2)
FROM   (SELECT ROWNUM CNT
             , RETURN#
        FROM   (SELECT '홍길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '김길동' RETURN#
                FROM   DUAL
                UNION ALL
                SELECT '이길동' RETURN#
                FROM   DUAL))
START WITH RETURN# = '홍길동'
CONNECT BY PRIOR CNT = CNT - 1  
;
        
드디어 우리가 원하는 결과가 나왔습니다. 이 쿼리라면 100만개 로우가 나온다고 하여도 문제가 없겠군요.

지금까지 김홍선님이 올리신 쿼리에 대해 풀이를 해 보았습니다. 쓰고나니 장황하게 벌여놓은거 같아서 지저분하지만
이 글을 보시는 모든분들이 이해하기 쉽도록 풀어서 쓰느라 이렇게 되었습니다. 앞으로도 틈틈히 시간을 내어서 다른 좋은 내용들을 가지고
풀이를 써보도록 하겠습니다.


지금까지 긴 글 읽어주셔서 감사합니다.

[원문] http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=57#517