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시에만 포함이 되어있죠.
쿼리 돌려보시면 금방 이해가 갈듯 합니다.
그러므로 자세한 설명은 생략!!
'SQL' 카테고리의 다른 글
| LEFT OUTER JOIN을 이용한 컬럼 합치기(?) (1) | 2017.08.31 |
|---|---|
| [비공개] Oracle 대용량 insert (0) | 2017.06.29 |
| Oracle dynamic date 오라클 동적 시간 만들기 (0) | 2017.06.28 |
| IINNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN (0) | 2017.02.15 |
| ORACLE WITH (0) | 2017.01.25 |