Develop의 Tistory

오라클의 여러행을 하나의 컬럼으로 합치는 쿼리를 메모하고져 글을 남깁니다.

자세한 설명은 하지 않겠습니다.

(XMLAGG 및 정규식은 10g부터 사용 가능)

• WM_CONCAT

SELECT
 WM_CONCAT(job) AS WM_JOBS
 FROM emp

• LISTAGG

SELECT
 LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
FROM emp

• LISTAGG (deptno 기준)

SELECT
 LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) OVER(PARTITION BY deptno) AS AGG_JOBS2
FROM emp

• LISTAGG (중복제거)

SELECT
 LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
 , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,1円)*(,|$)', '1円3円') AS AGG_JOBS3
FROM emp

• XMLAGG, XMLELEMENT (CLOB타입 - WM_CONCAT, LISTAGG : VARCHAR2라 최대 4000byte)

SELECT
 XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS
FROM emp A

• XMLAGG, XMLELEMENT (CLOB타입, 중복제거, 시작/끝 구분자 삭제)

WITH A AS
(
SELECT
 XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
 , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT 
 SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
 , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
 , SUBSTR(REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,1円)*(,|$)', '1円3円'), 2) AS SUB_JOB3
FROM A

• 통합 (LISTAGG -PARTITION 제외)

WITH A AS
(
SELECT
 WM_CONCAT(job) AS WM_JOBS
 , LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
 , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,1円)*(,|$)', '1円3円') AS AGG_JOBS3
 , XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
 , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT
 A.*
 , SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
 , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
 , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS1, '([^,]+)(,1円)*(,|$)', '1円3円'), '(,)$', '') AS SUB_JOB3
 , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,1円)*(,|$)', '1円3円'), '^(,)', '') AS SUB_JOB4
FROM A

※(注記) ORA-01489 문자열 연결의 결과가 너무 깁니다.

: 오류 발생시 XMLAGG를 이용하여 CLOB 사용.

'DBMS > Oracle' 카테고리의 다른 글

Tag

'DBMS/Oracle'의 다른글

• 이전글[ORACLE]"ORA-12541 : TNS 리스너가 없습니다." - 특별한 이유 없이 발생하는 경우
• 현재글[ORACLE/SQL] 여러 행을 컬럼 합치기 (WM_CONCAT, LISTAGG, XMLAGG, XMLELEMENT, 중복제거)
• 다음글[ORACLE/SQL] 문자열 값을 숫자타입으로 변경 (VARCHAR TO NUMBER)

관련글

• [ORALCE/PLAN] 오라클 Plan Table(실행계획) 생성 및 권한 부여 2021年08月19日
• [ORACLE/SQL] 문자열 값을 숫자타입으로 변경 (VARCHAR TO NUMBER) 2021年07月06日
• [ORACLE]"ORA-12541 : TNS 리스너가 없습니다." - 특별한 이유 없이 발생하는 경우 2021年04月20日
• ODP.NET 설치 안하고 클라이언트 배포 2012年07月13日