데이터베이스 질문과 답변
by 불장작 [2023.06.12 14:33:50]
예제)
WITH t AS
(
SELECT '07:10' apnt_hm, '2023-06-12' apnt_dt from dual union all
SELECT '07:10' apnt_hm, '2023-06-13' apnt_dt from dual union all
SELECT '07:10' apnt_hm, '2023-06-14' apnt_dt from dual union all
SELECT '07:10' apnt_hm, '2023-06-15' apnt_dt from dual union all
SELECT '07:20' apnt_hm, '2023-06-12' apnt_dt from dual union all
SELECT '07:20' apnt_hm, '2023-06-16' apnt_dt from dual union all
SELECT '07:20' apnt_hm, '2023-06-17' apnt_dt from dual
)
SELECT *
FROM (SELECT apnt_hm, apnt_dt
, DENSE_RANK() OVER(ORDER BY apnt_dt) dr
FROM t
)
PIVOT (MIN(apnt_dt) APNT_DT FOR dr IN (1, 2, 3, 4, 5, 6))
ORDER BY 1
==> 결과
APNT_HM 1_APNT_DT 2_APNT_DT 3_APNT_DT 4_APNT_DT 5_APNT_DT 6_APNT_DT
07:10 2023-06-12 2023-06-13 2023-06-14 2023-06-15
07:20 2023-06-12 2023-06-16 2023-06-17
위의 컬럼명을 1_APNT_DT를 APNT_DT1, 2_APNT_DT를 APNT_DT2 ....로 변경할 수 있을까요?
그리고 IN (1, 2, 3, 4, 5, 6) 을 좀 더 고급스럽게 6개를 쿼리로 처리는 불가능할까요?
고수님의 조언 부탁드립니다.
SELECT *
FROM (SELECT apnt_hm, apnt_dt
, DENSE_RANK() OVER(ORDER BY apnt_dt) dr
FROM t
)
PIVOT (MIN(apnt_dt) FOR dr IN ( 1 apnt_dt1
, 2 apnt_dt2
, 3 apnt_dt3
, 4 apnt_dt4
, 5 apnt_dt5
, 6 apnt_dt6
) )
ORDER BY apnt_hm
;
SELECT apnt_hm
, MIN(DECODE(dr, 1, apnt_dt)) apnt_dt1
, MIN(DECODE(dr, 2, apnt_dt)) apnt_dt2
, MIN(DECODE(dr, 3, apnt_dt)) apnt_dt3
, MIN(DECODE(dr, 4, apnt_dt)) apnt_dt4
, MIN(DECODE(dr, 5, apnt_dt)) apnt_dt5
, MIN(DECODE(dr, 6, apnt_dt)) apnt_dt6
FROM (SELECT apnt_hm, apnt_dt
, DENSE_RANK() OVER(ORDER BY apnt_dt) dr
FROM t
)
GROUP BY apnt_hm
ORDER BY apnt_hm
;
행을 열로 바꿀 때 열의 개수는 고정입니다.
가변 적용은 순수 SQL 만으로는 안됩니다.
동적 쿼리를 적용해야만 가능합니다.
마농님 답변 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.