아래와 같은 데이터가 있습니다.
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' ) )