이전에 건우아빠 님께서 답변을 주셨었는데... 제가 질문을 잘못 드린것 같습니다;;
어떻게든 답변 주신 내용으로 해보려고 했는데........ 제 능력치로는 역부족이어서 다시 질문 올립니다;;
밑에와 같은 형태의 테이블이 있습니다.
| idnum | mdate | gtype | memo | price |
| 1 | 2017-03 | 1 | 테스트1 | 10,000 |
| 1 | 2017-03 | 2 | 테스트2 | 20,000 |
| 1 | 2017-03 | 3 | 테스트3 | 30,000 |
| 2 | 2017-04 | 2 | 테스트4 | 40,000 |
| 2 | 2017-04 | 3 | 테스트5 | 50,000 |
| 3 | 2017-01 | 1 | 테스트6 | 60,000 |
| 3 | 2017-02 | 1 | 테스트7 | 70,000 |
| 4 | 2017-01 | 2 | 테스트8 | 80,000 |
| 4 | 2017-05 | 1 | 테스트9 | 90,000 |
| 4 | 2017-05 | 3 | 테스트10 | 100,000 |
idnum별로 가로 데이터를 만드는데, mdate+gtype별 memo와 sum(price)를 만들어야 합니다.
| idnum | month01_1 | month01_2 | month01_3 | sum01 | month02_1 | month02_2 | month02_3 | sum02 | month03_1 | month03_2 | month03_3 | sum03 | month04_1 | month04_2 | month04_3 | sum04 | month05_1 | month05_2 | month05_3 | sum05 | month06_1 | month06_2 | month06_3 | sum06 |
| 1 | 테스트1 | 테스트2 | 테스트3 | 60,000 | ||||||||||||||||||||
| 2 | 테스트4 | 테스트5 | 90,000 | |||||||||||||||||||||
| 3 | 테스트6 | 60,000 | 테스트7 | 70,000 | ||||||||||||||||||||
| 4 | 테스트8 | 80,000 | 테스트9 | 테스트10 | 190,000 |
mdate가 03월 이며, gtype이 1이면 month03_1 이라는 컬럼에 해당 memo가 들어가고,
sum03에 mdate가03월인 모든 gtype 의 price 의 합계가 들어가야 합니다....
컬럼은 값이 있던 없던 1월부터 12월까지 있어야 하고요...
고수님들의 답변 꼭 부탁드립니다...
※ 그냥 무식하게 월별로 join 해서 만드니 돌리는데 어마어마한 시간이 걸리네요;;
Comment 1
-
건우아빠
2017.10.24 17:16
gtype가 가변이면 동적 쿼리로 가야하지만
어느 정도는 무식하게 고정하샤도 무방할듯 합니다.
with res as
(
select 1 idnum,'2017-03' mdate,'1' gtype, '테스트1' memo, 10000 price union all
select 1 ,'2017-03' ,'2' , '테스트2' , 20000 union all
select 1 ,'2017-03' ,'3' , '테스트3' , 30000 union all
select 2 ,'2017-04' ,'2' , '테스트4' , 40000 union all
select 2 ,'2017-04' ,'3' , '테스트5' , 50000 union all
select 3 ,'2017-01' ,'1' , '테스트6' , 60000 union all
select 3 ,'2017-02' ,'1' , '테스트7' , 70000 union all
select 4 ,'2017-01' ,'2' , '테스트8' , 80000 union all
select 4 ,'2017-05' ,'1' , '테스트9' , 90000 union all
select 4 ,'2017-05' ,'3' , '테스트10' , 100000
)
select idnum
, max([month01_1]) [month01_1] , max([month01_2]) [month01_2] , max([month01_3]) [month01_3] , sum([sum01] ) [sum01]
, max([month02_1]) [month02_1] , max([month02_2]) [month02_2] , max([month02_3]) [month02_3] , sum([sum02] ) [sum02]
, max([month03_1]) [month03_1] , max([month03_2]) [month03_2] , max([month03_3]) [month03_3] , sum([sum03] ) [sum03]
, max([month04_1]) [month04_1] , max([month04_2]) [month04_2] , max([month04_3]) [month04_3] , sum([sum04] ) [sum04]
, max([month05_1]) [month05_1] , max([month05_2]) [month05_2] , max([month05_3]) [month05_3] , sum([sum05] ) [sum05]
, max([month06_1]) [month06_1] , max([month06_2]) [month06_2] , max([month06_3]) [month06_3] , sum([sum06] ) [sum06]
, max([month07_1]) [month07_1] , max([month07_2]) [month07_2] , max([month07_3]) [month07_3] , sum([sum07] ) [sum07]
, max([month08_1]) [month08_1] , max([month08_2]) [month08_2] , max([month08_3]) [month08_3] , sum([sum08] ) [sum08]
, max([month09_1]) [month09_1] , max([month09_2]) [month09_2] , max([month09_3]) [month09_3] , sum([sum09] ) [sum09]
, max([month10_1]) [month10_1] , max([month10_2]) [month10_2] , max([month10_3]) [month10_3] , sum([sum10] ) [sum10]
, max([month11_1]) [month11_1] , max([month11_2]) [month11_2] , max([month11_3]) [month11_3] , sum([sum11] ) [sum11]
, max([month12_1]) [month12_1] , max([month12_2]) [month12_2] , max([month12_3]) [month12_3] , sum([sum12] ) [sum12]
from (
select idnum
-- , mdate
-- , gtype
, memo
, 'month'+RIGHT(mdate,2) +'_'+ gtype nmont_col
, 'sum'+RIGHT(mdate,2) nmont_col2
, price
-- , SUM(price) OVER (partition by RIGHT(mdate,2) ) sumprice
from res )As P
PIVOT (
max(memo)
FOR [nmont_col] IN ( [month01_1],[month01_2],[month01_3]
,[month02_1],[month02_2],[month02_3]
,[month03_1],[month03_2],[month03_3]
,[month04_1],[month04_2],[month04_3]
,[month05_1],[month05_2],[month05_3]
,[month06_1],[month06_2],[month06_3]
,[month07_1],[month07_2],[month07_3]
,[month08_1],[month08_2],[month08_3]
,[month09_1],[month09_2],[month09_3]
,[month10_1],[month10_2],[month10_3]
,[month11_1],[month11_2],[month11_3]
,[month12_1],[month12_2],[month12_3]
)
) AS PVT1
PIVOT (
sum(price)
FOR [nmont_col2] IN ( [sum01],[sum02] ,[sum03] ,[sum04] ,[sum05],[sum06]
,[sum07],[sum08] ,[sum09] ,[sum10] ,[sum11],[sum12]
)
) AS PVT2
group by idnum