[DB] LISTAGG 사용 - 열거형쿼리

PARTITION BY 와 CONNECT BY를 같이 사용 하는 것보다 쿼리가 간결해집니다

사용법은 간단하고요

현재 버전의 DB2와 ORACLE, MS-SQL에도 적용이 가능합니다

SELECT LISTAGG( FRUIT, ', ') WITHIN GROUP (ORDER BY FRUIT) AS HOLD_CNT

FROM (

SELECT 'A' AS GROUPKEY, '사과' AS FRUIT

FROM DUAL

UNION ALL

SELECT 'A' AS GROUPKEY, '오렌지' AS FRUIT

FROM DUAL

UNION ALL

SELECT 'A' AS GROUPKEY, '포도' AS FRUIT

FROM DUAL

UNION ALL

SELECT 'A' AS GROUPKEY, '레몬' AS FRUIT

FROM DUAL

) T

GROUP BY T.GROUPKEY