AGGR_CONCAT, REPLACE, CHR 함수를 이용한 쿼리 만들기

AGGR_CONCAT, REPLACE, CHR 함수로 테이블 내의 데이터를 원하는대로 나열할 수 있다.

AGGR_CONCAT을 이용하면 여러 행의 데이터를 하나의 컬럼으로 모아서 보여줄 수 있다.

SELECT AGGR_CONCAT(LEVEL,',')
FROM DUAL
CONNECT BY LEVEL < 10

위 쿼리를 수행하면 다음과 같이 한 행에 모든 행의 값을 ,로 구분해서 반환해준다.

AGGR_CONCAT(LEVEL,',')
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9

1 row selected.

USER_TAB_COLUMNS와 같은 뷰에 활용하면 효과적인데, 특정 DATA_TYPE만 선택해서 쿼리를 생성하거나 DATA_TYPE에 따라 별도의 처리를 해줄 수 있다.

DBA_DATA_FILES 뷰에서 DATA_TYPE이 숫자인 컬럼만 골라서 조회하는 쿼리를 AGGR_CONCAT 함수를 이용해서 만들어낼 수 있다. 다음과 같이 조회하면

SELECT 'SELECT ' ||
        AGGR_CONCAT(COLUMN_NAME,',') ||
        ' FROM DBA_DATA_FILES'
FROM
        DBA_TAB_COLUMNS
WHERE TABLE_NAME='DBA_DATA_FILES'
        AND DATA_TYPE='NUMBER'

아래와 같은 결과를 받을 수 있다.

'SELECT'||AGGR_CONCAT(COLUMN_NAME,',')||'FROMDBA_DATA_FILES'
--------------------------------------------------------------------------------
SELECT FILE_ID,BYTES,BLOCKS,RELATIVE_FNO,MAXBYTES,MAXBLOCKS,INCREMENT_BY FROM DB
A_DATA_FILES

1 row selected.

linesize가 충분하지 않다면, 주어진 linesize 만큼 개행이 되어 추가적인 수작업이 필요할 수 있는데 이때 REPLACE와 CHR 함수를 이용하면 적당히 처리하기 쉽게 개행을 시킬 수 있다.

다음과 같이 ','를 ','||CHR(10)||CHR(13) 으로 변경시켜주면 ,를 ,\n으로 변경시킴으로써, 의도적으로 개행을 시켜 linesize로 인해 문자열이 망가지는 문제를 해결할 수 있다.

SELECT 'SELECT ' ||
        REPLACE(
                AGGR_CONCAT(COLUMN_NAME,',')
                ,',',','||CHR(10)||CHR(13)
        )||
        ' FROM DBA_DATA_FILES'
FROM
        DBA_TAB_COLUMNS
WHERE TABLE_NAME='DBA_DATA_FILES'
        AND DATA_TYPE='NUMBER'

위 쿼리를 수행하면 다음과 같은 결과를 반환받는다.

'SELECT'||REPLACE(AGGR_CONCAT(COLUMN_NAME,','),',',','||CHR(10)||CHR(13))||'FROM
--------------------------------------------------------------------------------
SELECT FILE_ID,
BYTES,
BLOCKS,
RELATIVE_FNO,
MAXBYTES,
MAXBLOCKS,
INCREMENT_BY FROM DBA_DATA_FILES

1 row selected.

자주 사용하는 CHR

• CHR(9)
  • \t (탭)
• CHR(10)
  • \n (줄바꿈)
• CHR(13)
  • \r (행의 시작)
• CHR(38)
  • & (앰퍼샌드)
• CHR(39)
  • ' (따옴표)
• CHR(44)
  • , (쉼표)

Oracle 에서는 LISTAGG, Impala에서는 GROUP_CONCAT 함수를 이용해서 AGGR_CONCAT 과 같은 기능을 할 수 있다.