[ORACLE 함수 (LISTAGG) 집계함수 : 여러행을 한행에 보여주기]

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])

LISTAGG함수는 여러행으로 조회되는 것을 한 행으로 합쳐서 보여줄 때 용이하게 사용된다.

때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용된다.

 

 

WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT * FROM TEST_DB;

 

 

 

WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT CROOM, LISTAGG(NM, ',') WITHIN GROUP(ORDER BY NM) AS NMS
FROM TEST_DB
GROUP BY CROOM;