데이터베이스 질문과 답변
by 푸릉이 [2018.09.04 17:06:56]
아래와 같은 데이터가 있습니다.
WITH T(YYMM, DEPT, BEONHO, AYMD, AHOUR, AMIN, ASEC, BYMD, BHOUR, BMIN, BSEC, CYMD, CHOUR, CMIN, CSEC ) AS
(
SELECT '201801', 'depta', '1', '20180101', '12', '30', '15', '20180101', '12', '40', '18', '20180101', '12', '55', '17' FROM DUAL UNION ALL
SELECT '201802', 'deptb', '1', '20180213', '01', '11', '22', '20180101', '01', '30', '54', '20180101', '01', '50', '43' FROM DUAL
)
SELECT * FROM T
WHERE YYMM BETWEEN '201801' AND '201803'
최종결과는
YYMM DEPT BEONHO GUBUN FULLTIME(일시분 붙인거) SEC
201801 DEPTA 1 A 201801011230 15
201801 DEPTA 1 B 201801011240 18
201801 DEPTA 1 C 201801011255 17
201802 DEPTB 1 A 201802130111 22
201802 DEPTB 1 B 201802130130 54
201802 DEPTB 1 C 201802130150 43
이런식으로 시간을 붙여서 ROW로 쭉 뽑아내고 싶은데 UNION ALL 외에 깔끔하게 하는 방법 있을까요?
WITH T(YYMM, DEPT, BEONHO, AYMD, AHOUR, AMIN, ASEC, BYMD, BHOUR, BMIN, BSEC, CYMD, CHOUR, CMIN, CSEC ) AS
(
SELECT '201801', 'depta', '1', '20180101', '12', '30', '15', '20180101', '12', '40', '18', '20180101', '12', '55', '17' FROM DUAL UNION ALL
SELECT '201802', 'deptb', '1', '20180213', '01', '11', '22', '20180101', '01', '30', '54', '20180101', '01', '50', '43' FROM DUAL
)
SELECT YYMM , DEPT , BEONHO , GUBUN , VYMD || VHOUR FULLTIME , VSEC AS SEC
FROM (SELECT *
FROM T
WHERE YYMM BETWEEN '201801' AND '201803'
)
UNPIVOT ( ( VYMD , VHOUR , VSEC ) FOR GUBUN IN ( ( AYMD , AHOUR , ASEC ) AS 'A' ,
( BYMD , BHOUR , BSEC ) AS 'B' ,
( CYMD , CHOUR , CSEC ) AS 'C'
)
)
분을 빼먹었네요.. ㅡㅡ;
고맙습니다. 분 추가해서 작성했습니다. ^^
SELECT yymm
, dept
, beonho
, gubun
, vymd || vhour || vmin AS fulltime
, sec
FROM t
UNPIVOT ( (vymd, vhour, vmin, sec) FOR gubun IN ( (aymd, ahour, amin, asec) AS 'A'
, (bymd, bhour, bmin, bsec) AS 'B'
, (cymd, chour, cmin, csec) AS 'C'
) )
WHERE yymm BETWEEN '201801' AND '201803'
;
오오오 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.