with A
as
(
select '2017-01' as ym, 10 as goods1, 15 as goods2, 20 as goods3, 3500 as price1, 5000 as price2, 2000 as price3
union all
select '2017-02', 15, 30, 18, 3500, 5000, 2000
union all
select '2017-03', 21, 20, 17, 3500, 5000, 2000
union all
select '2017-04', 20, 10, 16, 3500, 5000, 2000
union all
select '2017-05', 27, 9, 15, 3500, 5000, 2000
union all
select '2017-06', 12, 8, 14, 3500, 5000, 2000
)
select * from A
ym | goods1 | goods2 | goods3 | price1 | price2 | price3 |
2017-01 | 10 | 15 | 20 | 35000 | 5000 | 2000 |
2017-02 | 15 | 30 | 18 | 35000 | 5000 | 2000 |
2017-03 | 21 | 20 | 17 | 35000 | 5000 | 2000 |
2017-04 | 20 | 10 | 16 | 3500 | 5000 | 2000 |
2017-05 | 27 | 9 | 15 | 3500 | 5000 | 2000 |
2017-06 | 12 | 8 | 14 | 3500 | 5000 | 2000 |
이런 형식을
colmn | 2017-01 | 2017-02 | 2017-03 | 2017-04 | 2017-05 | 2017-06 |
goods1 | 10 | 15 | 21 | 20 | 27 | 12 |
price1 | 3500 | 3500 | 3500 | 3500 | 3500 | 3500 |
goods2 | 15 | 30 | 20 | 10 | 9 | 8 |
price2 | 5000 | 5000 | 5000 | 5000 | 5000 | 5000 |
goods3 | 20 | 18 | 17 | 16 | 15 | 14 |
price3 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 |
이렇게 바꾸고 싶습니다.
검색해보니.. pivot 쓰라고 해서.. 2틀동안 예시들 쫒아해보는데........ 도저히 안되어서;;;
답변 꼭 좀 부탁드립니다.
Comment 3
-
축구선수
2018.02.22 15:51
제가 찾은 방식은 피봇 사용 안하고아래 방식으로도 전환이 가능하긴 한데요....너무 쿼리가 길어지고 지저분해져서....다른 상품들은 같은 방식으로 아래에 다가 유니온 시키시면 됩니다.with Aas(select '2017-01' as ym, 10 as goods1, 15 as goods2, 20 as goods3, 3500 as price1, 5000 as price2, 2000 as price3union allselect '2017-02', 15, 30, 18, 3500, 5000, 2000union allselect '2017-03', 21, 20, 17, 3500, 5000, 2000union allselect '2017-04', 20, 10, 16, 3500, 5000, 2000union allselect '2017-05', 27, 9, 15, 3500, 5000, 2000union allselect '2017-06', 12, 8, 14, 3500, 5000, 2000)select * from-- A(select 'goods1' AS COL, MAX(CASE WHEN YM = '2017-01' THEN goods1 END ) AS '2017-01', MAX(CASE WHEN YM = '2017-02' THEN goods1 END ) AS '2017-02', MAX(CASE WHEN YM = '2017-03' THEN goods1 END ) AS '2017-03', MAX(CASE WHEN YM = '2017-04' THEN goods1 END ) AS '2017-04', MAX(CASE WHEN YM = '2017-05' THEN goods1 END ) AS '2017-05', MAX(CASE WHEN YM = '2017-06' THEN goods1 END ) AS '2017-06'from A) T -
건우아빠
2018.02.22 23:56
unpivot 으로 goods prices를 행으로 변환한신 다음 pivot으로 하실수있을듯 -
건우아빠
2018.02.23 10:16
withres as(select '2017-01' as ym, 10 as goods1, 15 as goods2, 20 as goods3, 3500 as price1, 5000 as price2, 2000 as price3union allselect '2017-02', 15, 30, 18, 3500, 5000, 2000union allselect '2017-03', 21, 20, 17, 3500, 5000, 2000union allselect '2017-04', 20, 10, 16, 3500, 5000, 2000union allselect '2017-05', 27, 9, 15, 3500, 5000, 2000union allselect '2017-06', 12, 8, 14, 3500, 5000, 2000) ,res2 as(select * , RIGHT(col_nm,1) gbfrom resunpivot (val for col_nm in ( goods1, goods2, goods3, price1, price2, price3)) as B)select *from res2pivot (SUM(val ) FOR ym IN ([2017-01],[2017-02],[2017-03],[2017-04],[2017-05],[2017-06])) corder by gb , col_nm
