다중 행을 문자열로 집계

다중 행을 문자열로 집계해보자.

 

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);

INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (3, 4);
INSERT INTO t1 VALUES (3, 4);
INSERT INTO t1 VALUES (3, 5);
INSERT INTO t1 VALUES (3, 6);
COMMIT;

 

10.1 이전 버전은 아래와 같이 계층형 쿼리를 사용해야 한다. 

-- 2
SELECT     c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ',')), 2) AS c2
      FROM (SELECT c1, c2, ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) AS rn
              FROM t1)
START WITH rn = 1
CONNECT BY c1 = PRIOR c1
       AND rn - 1 = PRIOR rn
  GROUP BY c1
  ORDER BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,4,5,6

3 행이 선택되었습니다.

 

10.1 버전부터 WMSYS.WM_CONCAT 함수를 사용할 수 있다. WMSYS 사용자가 내부적으로 사용하는 함수다. 다양한 기능을 제공하지만 12.1 버전부터 사용할 수 없다.^[각주:1]

-- 3-1
SELECT   c1, wmsys.wm_concat (c2) AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,6,5,4

3 행이 선택되었습니다.

-- 3-2
SELECT   c1, wmsys.wm_concat (DISTINCT c2) AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- -----
 1 1
 2 2,3
 3 4,5,6

3 행이 선택되었습니다.

-- 3-3
SELECT c1, wmsys.wm_concat (c2) OVER (ORDER BY c2) AS c2
  FROM t1;

C1 C2
-- -------------
 1 1
 2 1,2
 2 1,2,3
 3 1,2,3,4,4
 3 1,2,3,4,4
 3 1,2,3,4,4,5
 3 1,2,3,4,4,5,6

7 행이 선택되었습니다.

-- 3-4
SELECT   c1, MAX (CAST (c2 AS VARCHAR2 (4000))) as c2
    FROM (SELECT c1, wmsys.wm_concat (c2) OVER (PARTITION BY c1 ORDER BY c2) AS c2
            FROM t1)
GROUP BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,4,5,6

3 행이 선택되었습니다.

-- 3-5
SELECT   c1, wmsys.wm_concat (c2) KEEP (DENSE_RANK FIRST ORDER BY c2) AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- ---
 1 1
 2 2
 3 4,4

3 행이 선택되었습니다.

 

10.1 버전부터 XMLAGG 함수를 사용할 수 있다. CLOB 타입을 사용하면 4000자 이상의 문자열을 결합할 수 있다. 

-- 4
SELECT   c1, LTRIM (XMLCAST (XMLAGG (XMLELEMENT (x, ',', c2) ORDER BY c2) AS CLOB), ',') AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,4,5,6

3 행이 선택되었습니다.

 

11.1 버전부터 LISTAGG 함수를 사용할 수 있다.

-- 5-1
SELECT   c1, LISTAGG (c2, ',') WITHIN GROUP (ORDER BY c2) AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,4,5,6

3 행이 선택되었습니다.

-- 5-2
SELECT c1, LISTAGG (c2, ',') WITHIN GROUP (ORDER BY c2) OVER (PARTITION BY c1) AS c2
  FROM t1;

C1 C2
-- -------
 1 1
 2 2,3
 2 2,3
 3 4,4,5,6
 3 4,4,5,6
 3 4,4,5,6
 3 4,4,5,6

7 행이 선택되었습니다.

[2018-03-05]

 

12.2 버전부터 XMLAGG 함수 대신 JSON_ARRAYAGG 함수를 사용할 수 있다.

-- 6
SELECT   c1, REGEXP_REPLACE (JSON_ARRAYAGG (c2 ORDER BY c2 RETURNING CLOB), '\[|"|\]') AS c2
    FROM t1
GROUP BY c1;

C1 C2
-- -------
 1 1
 2 2,3
 3 4,4,5,6

3 행이 선택되었습니다.

1. 문서화되지 않은 기능을 가급적 사용하지 않는 편이 바람직하다. [본문으로]