데이터베이스 질문과 답변
by 띵똥 [2018.12.26 15:17:38]
SELECT '서울' AS A,
'17일(09시)' AS B,
'' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '서울' AS A,
'17일(14시)' AS B,
'' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '서울' AS A,
'' AS B,
'' AS C,
'20일(09시)' AS D
FROM DUAL
UNION ALL
SELECT '서울' AS A,
'' AS B,
'23일(09시)' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'21일(09시)' AS B,
'' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'' AS B,
'' AS C,
'25일(14시)' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'' AS B,
'' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'22일(09시)' AS B,
'22일(14시)' AS C,
'' AS D
FROM DUAL;
위의 쿼리가 아래와같이 나오게 하려면 어떻게 해야하나요?!
;
SELECT '서울' AS A,
'17일(09시)' AS B,
'23일(09시)' AS C,
'20일(09시)' AS D
FROM DUAL
UNION ALL
SELECT '서울' AS A,
'17일(14시)' AS B,
'' AS C,
'' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'21일(09시)' AS B,
'22일(14시)' AS C,
'25일(14시)' AS D
FROM DUAL
UNION ALL
SELECT '경기' AS A,
'22일(09시)' AS B,
'' AS C,
'' AS D
FROM DUAL
WITH t AS
(
SELECT '서울' a, '17일(09시)' b, '' c, '' d FROM dual
UNION ALL SELECT '서울', '17일(14시)', '' , '' FROM dual
UNION ALL SELECT '서울', '' , '' , '20일(09시)' FROM dual
UNION ALL SELECT '서울', '' , '23일(09시)', '' FROM dual
UNION ALL SELECT '경기', '21일(09시)', '' , '' FROM dual
UNION ALL SELECT '경기', '' , '' , '25일(14시)' FROM dual
UNION ALL SELECT '경기', '' , '' , '' FROM dual
UNION ALL SELECT '경기', '22일(09시)', '22일(14시)', '' FROM dual
)
SELECT *
FROM (SELECT a, gb, v
, ROW_NUMBER() OVER(PARTITION BY a, gb ORDER BY v) rn
FROM t
UNPIVOT (v FOR gb IN (b, c, d))
)
PIVOT (MIN(v) FOR gb IN ('B' b, 'C' c, 'D' d))
;
) 버튼을 클릭하여 작성 하시면 됩니다.