ORACLE|오라클 레코드 한 문자열로 만들기(XMLAGG, LISTAGG 사용)

출처 : 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.

 

1. listagg function in 11g release 2

2. ORACLE-BASE - String Aggregation Techniques