Oracle10g - Xmlagg를 사용한 가로 Group by Query

C군의 질문

A | B
------
A | 가
B | 가
C | 가
a | 나
B | 나
C | 다

일때

가 | ABC
나 | aB
다 | C

를 뽑아낼 수 없을까?

라고 하여 요렇게 조렇게 실험해봤음.
일종의 String Aggregation 인데 http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 이런 방법도 있지만 왠지 복잡하고 10g 기능을 사용하고 싶어서 뒤져보기 시작.

(10g 가 아니라면 sys_connect_by_path 를 사용하는 방법이 무난. 비용이 좀 든다는 단점이 있다고 한다.)
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

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


XMLAGG 로 GROUP BY를 할 수 있다는 사실을 발견

SELECT B, XMLAGG(A) FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

하지만 이렇게 하면 오류가 난다.
타입이 맞지 않기 때문이다. 인자를 XML형으로 바꾸기 위해 XMLELEMENT를 사용한다.
컬럼에 C라는 Tag 명을 붙여서 보자.

SELECT B, XMLAGG(XMLELEMENT(C,A)) A FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

결과는 나오지만 충분하지 않다. 실제로 Application 에서 사용하려면 XML 타입이라 맞지 않기 때문이다.
GETSTRINGVAL()을 붙여서 형변환을 해준다.

SELECT B, XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL() A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
가 | ABC
나 | aB
다 | C

원하는 결과가 나왔다.
Tag 을 제거하자 기왕 xml도 건든 김에 정규표현식으로 제거해보자.


SELECT B, REGEXP_REPLACE(XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL(), '<[C/]+>', '') A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
------
가 | ABC
나 | aB
다 | C

게임오버 :)