데이터베이스 질문과 답변
테이블 -
날짜 그룹 칼럼
20250301 A 05
20250301 A 07
20250301 A 15
20250302 B 03
20250302 B 05
----------------------------------------------------------------결과-----------------------------------------------------
날짜 그룹 칼럼1 칼럼2 칼럼3 칼럼4 칼럼5 칼럼6 칼럼7 칼럼8 칼럼9 칼럼10 칼럼11 칼럼12 칼럼13 칼럼14 칼럼15 칼럼16 칼럼17
20250301 A Y Y Y
WITH t AS
(
SELECT '20250301' dt, 'A' gr, '05' v FROM dual
UNION ALL SELECT '20250301', 'A', '07' FROM dual
UNION ALL SELECT '20250301', 'A', '15' FROM dual
UNION ALL SELECT '20250302', 'B', '03' FROM dual
UNION ALL SELECT '20250302', 'B', '05' FROM dual
)
SELECT dt, gr
, MIN(DECODE(v, '01', 'Y')) v01
, MIN(DECODE(v, '02', 'Y')) v02
, MIN(DECODE(v, '03', 'Y')) v03
, MIN(DECODE(v, '04', 'Y')) v04
, MIN(DECODE(v, '05', 'Y')) v05
, MIN(DECODE(v, '06', 'Y')) v06
, MIN(DECODE(v, '07', 'Y')) v07
, MIN(DECODE(v, '08', 'Y')) v08
, MIN(DECODE(v, '09', 'Y')) v09
, MIN(DECODE(v, '10', 'Y')) v10
, MIN(DECODE(v, '11', 'Y')) v11
, MIN(DECODE(v, '12', 'Y')) v12
, MIN(DECODE(v, '13', 'Y')) v13
, MIN(DECODE(v, '14', 'Y')) v14
, MIN(DECODE(v, '15', 'Y')) v15
, MIN(DECODE(v, '16', 'Y')) v16
, MIN(DECODE(v, '17', 'Y')) v17
FROM t
GROUP BY dt, gr
ORDER BY dt, gr
;
WITH t AS
(
SELECT '20250301' dt, 'A' gr, '05' v FROM dual
UNION ALL SELECT '20250301', 'A', '07' FROM dual
UNION ALL SELECT '20250301', 'A', '15' FROM dual
UNION ALL SELECT '20250302', 'B', '03' FROM dual
UNION ALL SELECT '20250302', 'B', '05' FROM dual
)
SELECT *
FROM t
PIVOT (MIN('Y') FOR v IN ( '01' v01
, '02' v02
, '03' v03
, '04' v04
, '05' v05
, '06' v06
, '07' v07
, '08' v08
, '09' v09
, '10' v10
, '11' v11
, '12' v12
, '13' v13
, '14' v14
, '15' v15
, '16' v16
, '17' v17
) )
ORDER BY dt, gr
;