데이터베이스 질문과 답변
행을 열로 바꾸는 쿼리 질문이있습니다.
0
2
2,028
by 케를로스 [2017.03.19 15:01:53]
WITH COUNSELING_TABLE AS ( SELECT '상담사A' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170320' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생1'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'생활' AS COUNSELING_GB, '20170320' AS COUNSELING_DT, '1000' AS COUNSELING_ST_TIME,'학생2'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170320' AS COUNSELING_DT, '1400' AS COUNSELING_ST_TIME,'학생3'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사B' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170320' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생4'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사B' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170321' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생5'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170321' AS COUNSELING_DT, '1000' AS COUNSELING_ST_TIME,'학생6'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'생활' AS COUNSELING_GB, '20170322' AS COUNSELING_DT, '1000' AS COUNSELING_ST_TIME,'학생7' AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'생활' AS COUNSELING_GB, '20170323' AS COUNSELING_DT, '1400' AS COUNSELING_ST_TIME,'학생8'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사B' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170324' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생9'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'생활' AS COUNSELING_GB, '20170324' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생7'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사B' AS COUNSELING_NO,'진학' AS COUNSELING_GB, '20170327' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생1'AS COUNSELING_CONCERNED FROM DUAL UNION ALL SELECT '상담사A' AS COUNSELING_NO,'생활' AS COUNSELING_GB, '20170328' AS COUNSELING_DT, '0900' AS COUNSELING_ST_TIME,'학생2'AS COUNSELING_CONCERNED FROM DUAL ) SELECT * FROM COUNSELING_TABLE A
이런 상담예약 내역에서 월요일~금요일까지 시간표를 작성하려고 합니다.
하지만 행에서 열로 변경할때에는 집계쿼리를 사용을 해야하는데
집계쿼리를 사용하지않고도 변경할 수 있는 방법이있을지 궁굼해서 올립니다.
예상되는결과는
| 예약시간 | 월요일 | 화요일 | 수요일 | 목요일 | 금요일 |
| 09:00 |
상담사A,학생1 상담사B,학생4 |
상담사B,학생5 | NULL | NULL |
상담사B, 학생9 상담사A, 학생7 |
| 710:00 | 상담사A,학생2 | 상담사A,학생6 | 상담사A,학생7 | NULL | NULL |
| 14:00 | 상담사A,학생3 | NULL | NULL | 상담사A, 학생8 | NULL |
| 예약시간 | 월요일 | 화요일 | 수요일 | 목요일 | 금요일 |
| 09:00 |
상담사A,학생2 상담사B,학생1 |
NULL | NULL | NULL |
NULL |
이런식으로 데이터를 출력할 수 있는 방법이 있을까요??
by 마농
[2017.03.20 11:16:59]
행을 열로 바꾸는 것을 피벗이라 하는데요.
이전 버전에서는 Group By 와 Decode 를 함께 사용합니다.
11G 부터 PIVOT 구문이 있구요.
어차피 피벗 구문도 집계함수 사용합니다.
집계쿼리를 사용하지 않아야 하는 이유가 없을텐데요?
-- 1. Group By & Decode
SELECT 예약시간
, LISTAGG(DECODE(d, '2', x), CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x) 월
, LISTAGG(DECODE(d, '3', x), CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x) 화
, LISTAGG(DECODE(d, '4', x), CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x) 수
, LISTAGG(DECODE(d, '5', x), CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x) 목
, LISTAGG(DECODE(d, '6', x), CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x) 금
FROM (SELECT counseling_st_time 예약시간
, TO_CHAR(TO_DATE(counseling_dt, 'yyyymmdd'), 'd') d
, counseling_no ||','|| counseling_concerned x
FROM counseling_table
WHERE counseling_dt BETWEEN '20170320' AND '20170324'
)
GROUP BY 예약시간
ORDER BY 예약시간
;
-- 2. Pivot
SELECT *
FROM (SELECT counseling_st_time 예약시간
, TO_CHAR(TO_DATE(counseling_dt, 'yyyymmdd'), 'd') d
, counseling_no ||','|| counseling_concerned x
FROM counseling_table
WHERE counseling_dt BETWEEN '20170320' AND '20170324'
)
PIVOT (LISTAGG(x, CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY x)
FOR d IN ('2' 월, '3' 화, '4' 수, '5' 목, '6' 금) )
ORDER BY 예약시간
;
by 케를로스
[2017.03.20 13:59:28]
답변감사합니다 마농님 ^^
집계쿼리를 사용하지 못했던것은 일자,시간 별로 그룹을 줘야했던 부분때문에 예상되는 결과물과는 다르게 출력이되서
집계쿼리를 사용하지않아도 합칠 수 있는 방법이 있을까 해서 질문을 한 것 입니다.
) 버튼을 클릭하여 작성 하시면 됩니다.