출처 : http://acidsound.blogspot.com/2007/10/oracle10g-xmlagg-group-by-query.html
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
가 |
나 |
다 |
원하는 결과가 나왔다.
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
정규식을 사용하지 않고 태그 제거하기
WITH
TABLE AS (
SELECT 이름 FROM 유저목록
)
SELECT
LTRIM(SUBSTR(XMLAGG(XMLELEMENT(이름,'|'||이름 )).EXTRACT('//text()').GetStringVal(), 2))
FROM TABLE)
======================================================================================================
|
with tbl as ( |
제가 할려는게 같은 날짜의 title 를 seq 순서대로 뽑아내는건대요
위에 쿼리를 돌려보면
20110801 1||input1
20110802 2||input2,4||input4,5||input5,3||input3 <== 여기가 문제임
20110804 6||input6
이런식으로 나옵니다. 즉 2,3,4,5 이런순으로 나와야 하는대 엉뚱하게 3이 젤 나중에 나와버립니다.
어떻게 해야 할까요? 도움 주시면 감사하겠습니다...
|
wm_concat 간단하면서도 강력한 기능 정말 좋은데... |