출처 : http://raltigue.tistory.com/m/11
LISTAGG 함수가 도입되기 전에 동일 기능을 구현하기 위해 다양한 기법들이 사용되었다. 정리해보자.
아래와 같이 데이터를 생성하자.
CREATE TABLE t1 (c1 NUMBER(1), c2 VARCHAR2(2)); INSERT INTO t1 VALUES (1, '01'); INSERT INTO t1 VALUES (2, '02'); INSERT INTO t1 VALUES (2, '03'); INSERT INTO t1 VALUES (3, '04'); INSERT INTO t1 VALUES (3, '04'); INSERT INTO t1 VALUES (3, '05'); INSERT INTO t1 VALUES (3, '06');
① 11g를 사용한다면 LISTAGG 함수를 사용하면 된다. 집계함수(1번)와 분석함수(2번) 형태로 사용이 가능하다.
-- 1
SELECT a.c1,
LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) AS c2
FROM t1 a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,04,05,06
3 rows selected.
-- 2
SELECT a.c1,
LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) OVER (PARTITION BY A.c1) AS c2
FROM t1 a;
C1 C2
--- ------------
1 01
2 02,03
2 02,03
3 04,04,05,06
3 04,04,05,06
3 04,04,05,06
3 04,04,05,06
7 rows selected.
② WM_CONCAT 함수는 WMSYS 유저가 내부적으로 사용한다. (SQL Reference에 없다...--;) LISTAGG보다 성능은 떨어지지만 추가 기능(DISTINCT 구문, 분석함수 누적, KEEP 절)을 지원한다. 4번 방식을 이용하면 정렬도 가능하다.
-- 1
SELECT a.c1,
wmsys.wm_concat (a.c2) AS c2
FROM t1 a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,06,05,04
3 rows selected.
-- 2
SELECT a.c1,
wmsys.wm_concat (DISTINCT a.c2) AS c2
FROM t1 a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,05,06
3 rows selected.
-- 3
SELECT a.c1,
wmsys.wm_concat (a.c2) OVER (ORDER BY a.c2) AS c2
FROM t1 a;
C1 C2
--- ----------------------
1 01
2 01,02
2 01,02,03
3 01,02,03,04,04
3 01,02,03,04,04
3 01,02,03,04,04,05
3 01,02,03,04,04,05,06
7 rows selected.
-- 4
SELECT a.c1,
MAX (CAST (a.c2 AS VARCHAR2 (4000))) as c2
FROM (SELECT a.c1,
wmsys.wm_concat (a.c2) OVER (PARTITION BY a.c1 ORDER BY a.c2) AS c2
FROM t1 a) a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,04,05,06
3 rows selected.
-- 5
SELECT a.c1,
wmsys.wm_concat (a.c2) KEEP (DENSE_RANK FIRST ORDER BY a.c2) AS c2
FROM t1 a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02
3 04,04
3 rows selected.
③ 10g에서는 XMLAGG 함수를 사용해도 된다.
SELECT a.c1,
SUBSTR (XMLAGG (XMLELEMENT (a, ',', a.c2) ORDER BY a.c2).EXTRACT ('//text()'), 2) AS c2
FROM t1 a
GROUP BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,04,05,06
3 rows selected.
④ 후임자를 괴롭히고 싶다면 MODEL 절을 사용해도 된다...--;
SELECT a.c1,
RTRIM (a.c2, ',') as c2
FROM (SELECT c1,
c2,
rn
FROM t1 a
MODEL PARTITION BY (a.c1)
DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn)
MEASURES (CAST (a.c2 AS VARCHAR2(4000)) AS c2)
RULES (c2[ANY] ORDER BY rn DESC = c2[CV()] || ',' || c2[CV()+1])) a
WHERE a.rn = 1
ORDER BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,04,05,06
3 rows selected.
⑤ 9i에서는 전통적 방식인 ROW_NUMBER와 SYS_CONNECT_BY_PATH 조합을 사용하면 된다.
SELECT a.c1,
SUBSTR (MAX (SYS_CONNECT_BY_PATH (a.c2, ',')), 2) AS c2
FROM (SELECT a.c1,
a.c2,
ROW_NUMBER () OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn
FROM t1 a) a
START WITH a.rn = 1
CONNECT BY a.c1 = PRIOR a.c1
AND a.rn - 1 = PRIOR a.rn
GROUP BY a.c1
ORDER BY a.c1;
C1 C2
--- ------------
1 01
2 02,03
3 04,04,05,06
3 rows selected.