seongsland

EMP 테이블에서 부서별로 사원 이름을 ,(콤마)로 분리해 내는 Query입니다.

1번 방법
SELECT A.DEPTNO
    , SUBSTR(SYS_CONNECT_BY_PATH(ENAME, ','), 2) AS NAME_LIST
FROM (
    SELECT DEPTNO, ENAME
        , COUNT(*) OVER(PARTITION BY DEPTNO) CNT
        , ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) SEQ
    FROM EMP
    WHERE DEPTNO IS NOT NULL
    ) A
WHERE A.SEQ = A.CNT
START WITH A.SEQ = 1
CONNECT BY PRIOR A.SEQ + 1 = A.SEQ
    AND PRIOR A.DEPTNO = A.DEPTNO

-- 결과
DEPTNO    NAME_LIST  
10  CLARK,KING,MILLER
20  ADAMS,FORD,JONES,SCOTT,SMITH
30  ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

2번 방법
모 대충 이런식.. group by로 묶임..
전체적으로 방해가 된다면 따로 서브 select문으로 사용할것..
LTRIM (
            XMLAGG (XMLELEMENT (name_ID,
                                ',' || column명) ORDER BY
                                                                              (SELECT SORT_ORD
                                                                                 FROM 222222)).EXTRACT (
               '//text()').getStringVal (),
            ',')