oracle listagg 이용한 union 항목들 구분하기

SELECT

DT,

    MAX(GUBUN),

    LISTAGG(GUBUN,',') WITHIN GROUP(ORDER BY GUBUN) as GUBUN2

FROM

(

SELECT 

A.*    

FROM

(

SELECT '10:00' AS DT, 'A' AS GUBUN FROM DUAL

UNION ALL

SELECT '11:00' AS DT, 'A' AS GUBUN FROM DUAL

UNION ALL

SELECT '14:00' AS DT, 'A' AS GUBUN FROM DUAL

UNION ALL

SELECT '15:00' AS DT, 'A' AS GUBUN FROM DUAL

UNION ALL

SELECT '16:00' AS DT, 'A' AS GUBUN FROM DUAL

) A

UNION ALL

SELECT

B.*

FROM

(

SELECT '12:00' AS DT, 'B' AS GUBUN FROM DUAL

UNION ALL

SELECT '13:00' AS DT, 'B' AS GUBUN FROM DUAL

UNION ALL

SELECT '14:00' AS DT, 'B' AS GUBUN FROM DUAL

) B

UNION ALL

SELECT 

C.*

FROM

(

SELECT '13:00' AS DT, 'C' AS GUBUN FROM DUAL

UNION ALL

SELECT '14:00' AS DT, 'C' AS GUBUN FROM DUAL

UNION ALL

SELECT '15:00' AS DT, 'C' AS GUBUN FROM DUAL

) C

) ALL_T

GROUP BY DT

결과

DT  | MAX(GUBUN) | GUBUN2

10:00 A A

11:00 A A

12:00 B B

13:00 C B,C

14:00 C A,B,C

15:00 C A,C

16:00 A A

이렇게 listagg 함수를 이용하면 그루핑할때

해당 그룹에 포함이 된 테이블과 아닌 테이블을 구분할 수 있습니다.

B 테이블의 경우 12시 13시 14시에만 포함이 되어있죠.

쿼리 돌려보시면 금방 이해가 갈듯 합니다.

그러므로 자세한 설명은 생략!!