환절기에 고생이 많으십니다.
MSSQL 아래와 같이 조회 결과를 변경하고 싶습니다.
어떻게 해야 할까요? PIVOT을 써도 잘 안되고 CASE 문을 써도 잘 안되는데
고수님들의 도움 부탁드립니다.
감사합니다.
변경전
DATE | X1 | X2 |
20190109 | 33.4 | 33.3 |
20190109 | 33.0 | 33.1 |
20190111 | 33.3 | 33.5 |
20190111 | 33.9 | 34.0 |
20190113 | 33.2 | 33.4 |
20190113 | 32.9 | 33.2 |
20190119 | 33.0 | 33.3 |
20190119 | 33.5 | 33.7 |
20190120 | 33.3 | 33.4 |
20190120 | 33.6 | 33.8 |
변경후
20190109 | 20190111 | 20190113 | 20190119 | 20190120 | ||||||
X1 | 33.4 | 33.0 | 33.3 | 33.9 | 33.2 | 32.9 | 33.0 | 33.5 | 33.3 | 33.6 |
X2 | 33.3 | 33.1 | 33.5 | 34.0 | 33.4 | 33.2 | 33.3 | 33.7 | 33.4 | 33.8 |
WITH t AS ( SELECT '20190109' dt, '01' tm, 33.4 x1, 33.3 x2 UNION ALL SELECT '20190109', '02', 33.0, 33.1 UNION ALL SELECT '20190111', '03', 33.3, 33.5 UNION ALL SELECT '20190111', '04', 33.9, 34.0 UNION ALL SELECT '20190113', '05', 33.2, 33.4 UNION ALL SELECT '20190113', '06', 32.9, 33.2 UNION ALL SELECT '20190119', '07', 33.0, 33.3 UNION ALL SELECT '20190119', '08', 33.5, 33.7 UNION ALL SELECT '20190120', '09', 33.3, 33.4 UNION ALL SELECT '20190120', '10', 33.6, 33.8 ) SELECT * FROM (SELECT CAST(dt AS VARCHAR(8)) DT , CAST(x1 AS VARCHAR(8)) X1 , CAST(x2 AS VARCHAR(8)) X2 , ROW_NUMBER() OVER(ORDER BY dt, tm) rn FROM t ) a UNPIVOT (x FOR gb IN (dt, x1, x2)) a PIVOT (MIN(x) FOR rn IN ( "1", "2", "3", "4", "5", "6", "7", "8", "9", "10" -- , "11", "12", "13", "14", "15", "16", "17", "18", "19", "20" ) ) a ;