오라클의 ROW를 COL 형태로 변환
상수가 물어봐서 한 김에 정리함
select usr
, max(mon01) as mon01
, max(mon02) as mon02
, max(mon03) as mon03
from (
select usr, lvl, amt
, case when lvl = '1' then amt else 0 end as mon01
, case when lvl = '2' then amt else 0 end as mon02
, case when lvl = '3' then amt else 0 end as mon03
from (
with usr as (
select 'lsk' as usr from dual
union all
select 'lss' as usr from dual
)
select *
from usr
, (select level as lvl, level * 1000 as amt from dual connect by level < 4) mon
)
)
group by usr
1. select level as lvl, level * 1000 as amt from dual connect by level < 4 : 1~3 까지 3개의 row을 생성함
2. usr와 1의 결과를 카테시안 으로 결과 나오게 함
3. 1의 개수만큼 case로 row 행 만듬
4. Usr 기준으로 group by 하여 col을 row로 압축시킴
상수가 알려준 케이스로 다시 한번 해봄
create table tmp_test(
id varchar2(100)
, mon varchar2(100)
, amt number
);
insert into tmp_test(id, mon, amt) values ( '20012899', '02', '3000');insert into tmp_test(id, mon, amt) values ( '20012899', '09', '3000');insert into tmp_test(id, mon, amt) values ( '20020076', '02', '3000');insert into tmp_test(id, mon, amt) values ( '20020076', '08', '3000');insert into tmp_test(id, mon, amt) values ( '20020612', '09', '3000');insert into tmp_test(id, mon, amt) values ( '20022761', '02', '3000');insert into tmp_test(id, mon, amt) values ( '20030271', '03', '3000');insert into tmp_test(id, mon, amt) values ( '20030271', '09', '3000');insert into tmp_test(id, mon, amt) values ( '20012899', '02', '3000');select id, sum(mon01) as mon01, sum(mon02) as mon02, sum(mon03) as mon03, sum(mon04) as mon04, sum(mon05) as mon05, sum(mon06) as mon06, sum(mon07) as mon07, sum(mon08) as mon08, sum(mon09) as mon09, sum(mon10) as mon10, sum(mon11) as mon11, sum(mon12) as mon12from (select t.id, t.mon, case when t.mon = '01' then t.amt else 0 end as mon01, case when t.mon = '02' then t.amt else 0 end as mon02, case when t.mon = '03' then t.amt else 0 end as mon03, case when t.mon = '04' then t.amt else 0 end as mon04, case when t.mon = '05' then t.amt else 0 end as mon05, case when t.mon = '06' then t.amt else 0 end as mon06, case when t.mon = '07' then t.amt else 0 end as mon07, case when t.mon = '08' then t.amt else 0 end as mon08, case when t.mon = '09' then t.amt else 0 end as mon09, case when t.mon = '10' then t.amt else 0 end as mon10, case when t.mon = '11' then t.amt else 0 end as mon11, case when t.mon = '12' then t.amt else 0 end as mon12from tmp_Test t) aagroup by id;