[10g] 동적쿼리를 활용한 UNPIVOT 구현(DLOOKUP)

/********************************************************************************************
-- Title : [10g] 동적쿼리를 활용한 UNPIVOT 구현(DLOOKUP)
-- Reference : asktop.oracle.com
-- Key word : 동적쿼리 동적 쿼리 unpivot pivot dlookup 피벗 언피벗 crosstab clss tab
********************************************************************************************/
-- 테이블 초기화
drop table temp purge;
drop table temp_lkup purge;

-- 테이블 생성
create table temp (id integer, a integer, b integer);
create table temp_lkup (id integer, col varchar2(16), calc varchar2(16));

-- 데이터 입력
insert into temp
select 1,11,12 from dual union all
select 2,14,15 from dual;
commit;

insert into temp_lkup
select 1, 'a', 'a' from dual union all
select 1, 'b', 'b*2' from dual union all
select 2, 'a', 'a+6' from dual union all
select 2, 'b', 'b*3' from dual;
commit;

-- 데이터 확인
select * from temp;
select * from temp_lkup;

-- 함수 생성
create or replace function fn_unpivot 
( afield in varchar2
, atable in varchar2
, awhere in varchar2 default null
, abind  in varchar2
) return varchar2
authid current_user
as
    ivar varchar2(4000);
    iquery varchar2(4000);
begin
    if afield is null then
        return null;
    end if;

    if atable is null then
        return null;
    end if;

    iquery := 'select ' || afield || ' from ' || atable;
      
    if (awhere is not null) then
        iquery := iquery ||' where '|| awhere;
    end if;
      
    execute immediate iquery into ivar using abind;
      
    return ivar;  
exception
    when no_data_found then
        return null;
    when others then
        raise;
end;

-- unpivot 확인
select a.id, b.col, b.calc
     , to_number(nvl(fn_unpivot(col, 'temp', 'id=:a', a.id),0)) orig
     , to_number(nvl(fn_unpivot(calc, 'temp', 'id=:a', a.id),0)) val
from temp a
inner join temp_lkup b
on a.id = b.id;