http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=58&page=2&position=2글쓴이 : 김홍선


예제 1)

다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법(1) 에서,
만약 행들이 그룹번호에 따라 그룹으로 나뉘어져 있고,
각 그룹별로 하나의 행으로 컴마로 분리해 출력하려고 하면 어떻게 해야 할 것인가?

이름    그룹
==========
홍길동  1
김길동  1  
이길동  1
홍순신  2
김순신  2
이순신  2
==========

-->

그룹 이름
======================
1   홍길동,김길동,이길동
2   홍순신,김순신,이순신
======================


emp.ename 컬럼, 그리고 그룹번호로 emp.deptno 컬럼을 예로 들어 쿼리를 구성해 보자.

emp 테이블의 deptno, ename 컬럼은 아래와 같다.

10    CLARK
10    KING
10    MILLER
20    ADAMS
20    FORD
20    JONES
20    SCOTT
20    SMITH
30    ALLEN
30    BLAKE
30    JAMES
30    MARTIN
30    TURNER
30    WARD


쿼리와 쿼리 결과는 아래와 같다.
쿼리에서 정확히 어떤 컬럼들이 어떤 역할을 하는지 이번에도 숙지하도록 하자.


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


10    CLARK,KING,MILLER
20    ADAMS,FORD,JONES,SCOTT,SMITH
30    ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD



예제 2)


이번에는 deptno 와 같이 그룹으로 나뉘는 컬럼이 2개(deptno,job) 일 때를 고려해 보자. (group by deptno, job)
3개 이상도 비슷한 방법으로 해결한다.
즉 아래의 테이블을,


10        CLERK      MILLER
10        MANAGER    CLARK
10        PRESIDENT  KING
---------------------------
20        ANALYST    FORD
20        ANALYST    SCOTT
20        CLERK      ADAMS
20        CLERK      SMITH
20        MANAGER    JONES
---------------------------
30        CLERK      JAMES
30        MANAGER    BLAKE
30        SALESMAN   ALLEN
30        SALESMAN   MARTIN
30        SALESMAN   TURNER
30        SALESMAN   WARD


아래와 같이 출력이 되도록 쿼리를 만들어 보자.


10    CLERK      MILLER
10    MANAGER    CLARK
10    PRESIDENT  KING
20    CLERK      ADAMS,SMITH
20    ANALYST    FORD,SCOTT
20    MANAGER    JONES
30    CLERK      JAMES
30    MANAGER    BLAKE
30    SALESMAN   ALLEN,MARTIN,TURNER,WARD


쿼리는 아래와 같다.
1번 예제의 쿼리에서 추가된 부분을 눈여겨 보자.
주의 : SYS_CONNECT_BY_PATH는 9i이상에서 가능함


SELECT     deptno, job,
           SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path#
      FROM (SELECT deptno, job, ename,
                   ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY ename) rnum
              FROM emp)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR deptno = deptno AND PRIOR job = job
  GROUP BY deptno, job