꿈꾸는 개발자, DBA 커뮤니티 구루비

행을 열로 바꾸는 쿼리 질문이있습니다.

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

이런식으로 데이터를 출력할 수 있는 방법이 있을까요??

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]

답변감사합니다 마농님 ^^

집계쿼리를 사용하지 못했던것은 일자,시간 별로 그룹을 줘야했던 부분때문에 예상되는 결과물과는 다르게 출력이되서

집계쿼리를 사용하지않아도 합칠 수 있는 방법이 있을까 해서 질문을 한 것 입니다.

데이터베이스 질문과 답변