[오라클/티베로]LISTAGG / AGGR_CONCAT 함수 사용

AGGR_CONCAT 함수(= LISTAGG 함수)

 
 

여러줄의 세로 데이타를  -> 가로로 표현하고자 할때 사용하는 함수입니다.

 
(예) 부서별 사용자를 표기하고자 하는 경우

 

* AGGR_CONCAT 함수 사용 

SELECT DEPT_NO, AGGR_CONCAT(USER_ID, ',') AS users
FROM 
(SELECT '부서1' DEPT_NO, 'a' AS USER_ID FROM DUAL
 UNION ALL
SELECT '부서2' DEPT_NO, 'b' AS USER_ID FROM DUAL
 UNION ALL
SELECT '부서1' DEPT_NO, 'c' AS USER_ID FROM DUAL
) TMP
GROUP BY DEPT_NO
 
=>  결과 

 
 

* LISTAGG 함수 사용

SELECT DEPT_NO, LISTAGG(USER_ID,',') WITHIN GROUP(ORDER BY USER_ID) AS users 
FROM 
(SELECT '부서1' DEPT_NO, 'a' AS USER_ID FROM DUAL
 UNION ALL
SELECT '부서2' DEPT_NO, 'b' AS USER_ID FROM DUAL
 UNION ALL
SELECT '부서1' DEPT_NO, 'c' AS USER_ID FROM DUAL
) TMP
GROUP BY DEPT_NO
 
=>  결과

 
 
각각 함수별 상세 사용법은 아래와 같습니다.
 

*  AGGR_CONCAT  함수

AGGR_CONCAT는 그룹 내의 모든 로우에 대해 문자열과 구분자를 접합하여 하나의 문자열로 만들어 반환하는 함수이다. NULL 값을 반환하는 파라미터는 결과로부터 제외된다.

 

 

 

AGGR_CONCAT의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  set_quantifier	질의 결과에 중복된 로우의 허용, 비허용 여부를 지정한다. DISTINCT, UNIQUE, ALL을 지정할 수 있다. DISTINCT, UNIQUE : 중복된 로우를 제거한다. ALL : 모든 로우를 선택한다. (기본값)
  expr	문자열이나 문자열로 변환될 수 있는 임의의 연산식이다.
  separator	expr과 접합될 구분자를 나타내는 문자 리터럴이다.
  order_by_clause	접합할 문자열을 어떻게 정렬할지를 명시한다.

• 예제

  SQL> SELECT AGGR_CONCAT(NAME, ',') AS "EMPLOYEE" FROM EMP
       GROUP BY DEPT_ID;
   
  EMPLOYEE
  ---------------------------------------------------------------------
  Johnny Depp,Brad Pitt,Bruce Willis
  Will Smith,Nicolas Cage
  Jason Statham
  Angelina Jolie
   
  4 rows selected.

 

 

*  LISTAGG  함수

LISTAGG는 AGGR_CONCAT와 동일한 기능을 하는 함수이다. Oracle과의 호환성을 위해 추가되었다.
AGGR_CONCAT과는 다음과 같은 사용법에 차이가 있다.

• set_quantifier 를 사용할 수 없다.
• separator를 생략할 수 있고 기본값은 NULL이다.
• order_by_clause는 WITHIN GROUP 이후에 명시하고 생략할 수 없다.

LISTAGG의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  expr	문자열이나 문자열로 변환될 수 있는 임의의 연산식이다.
  separator	expr과 접합될 구분자를 나타내는 문자 리터럴이다.
  order_by_clause	접합할 문자열을 어떻게 정렬할지를 명시한다.
  query_partition_clause	쿼리 결과에 대해 어떻게 그룹화 할지를 명시한다.

• 예제

  SQL> SELECT LISTAGG(ENAME, ',') WITHin GROUP (ORDER BY EMPNO) AS "EMPLOYEE"
     2 FROM EMP 
     3 GROUP BY DEPTNO;
  
  EMPLOYEE
  --------------------------------------------------------------------------------
  CLARK,KING,MILLER
  SMITH,JONES,SCOTT,ADAMS,FORD
  ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
  
  3 rows selected.

다음은 LISTAGG 함수를 사용하는 예이다.