반응형
/********************************************************************************************
-- Title : [10g] GROUPING SETS와 동적쿼리, 함수를 활용한 PIVOT 샘플
-- Reference : dbrang.tistory.com
-- Key word : grouping sets 동적쿼리 동적 쿼리 피벗 pivot cross tab crosstab
********************************************************************************************/
--테이블 초기화
drop table ttt purge;

-- 테이블 생성
create table ttt
( tech varchar2(6)
, nm varchar2(9)
, dt char(8)
);

-- 데이터 입력
insert into ttt
select '기계','김병만','20110117' from dual union all
select '기계','김병만','20110119' from dual union all
select '기계','김병만','20110120' from dual union all
select '기계','김병만','20110120' from dual union all
select '기계','김병만','20110126' from dual union all
select '기계','김종국','20110125' from dual union all
select '기계','김종국','20110126' from dual union all
select '기계','강개리','20110118' from dual union all
select '기계','강개리','20110119' from dual union all
select '기계','강개리','20110120' from dual union all
select '기계','강개리','20110125' from dual union all
select '기계','강개리','20110126' from dual union all
select '기계','박명수','20110117' from dual union all
select '기계','박명수','20110119' from dual union all
select '기계','박명수','20110120' from dual union all
select '기계','박명수','20110125' from dual union all
select '기계','박명수','20110126' from dual union all
select '기계','정준하','20110117' from dual union all
select '기계','정준하','20110119' from dual union all
select '기계','정준하','20110120' from dual union all
select '기계','정준하','20110125' from dual union all
select '기계','정준하','20110126' from dual union all
select '기계','정준하','20110126' from dual union all
select '기계','유희열','20110117' from dual union all
select '기계','유희열','20110120' from dual union all
select '기계','유희열','20110120' from dual union all
select '기계','유희열','20110125' from dual union all
select '기계','유희열','20110126' from dual union all
select '기계','하동훈','20110112' from dual union all
select '기계','하동훈','20110118' from dual union all
select '기계','하동훈','20110120' from dual union all
select '기계','하동훈','20110120' from dual union all
select '전자','서태지','20110117' from dual union all
select '전자','서태지','20110119' from dual union all
select '전자','서태지','20110126' from dual union all
select '전자','서태지','20110126' from dual union all
select '전자','강희섭','20110119' from dual union all
select '전자','심은하','20110112' from dual union all
select '전자','심은하','20110117' from dual union all
select '전자','심은하','20110118' from dual union all
select '전자','심은하','20110119' from dual union all
select '전자','심은하','20110120' from dual union all
select '전자','심은하','20110120' from dual union all
select '전자','심은하','20110126' from dual union all
select '전자','유재석','20110117' from dual union all
select '전자','유재석','20110119' from dual union all
select '전자','유재석','20110120' from dual union all
select '센터','장동건','20110117' from dual union all
select '센터','장동건','20110118' from dual union all
select '센터','장동건','20110120' from dual union all
select '센터','장동건','20110125' from dual union all
select '센터','장동건','20110126' from dual union all
select '센터','고소영','20110112' from dual union all
select '센터','고소영','20110117' from dual union all
select '센터','고소영','20110119' from dual union all
select '센터','고소영','20110112' from dual union all
select '센터','정명훈','20110117' from dual union all
select '센터','정명훈','20110119' from dual union all
select '센터','정명훈','20110126' from dual union all
select '센터','정명훈','20110126' from dual union all
select '센터','노우진','20110117' from dual union all
select '센터','노우진','20110119' from dual union all
select '센터','노우진','20110125' from dual;
commit;

-- 확인
select * from ttt
order by tech, nm, dt;

-- 출력 일자확인
select dt
from ttt
group by dt
order by dt;

/*********************
-- 정적쿼리
*********************/
-- pivoting
select tech, nm
     , sum(case when dt = '20110112' then 1 else 0 end) "c1"
     , sum(case when dt = '20110117' then 1 else 0 end) "c2"
     , sum(case when dt = '20110118' then 1 else 0 end) "c3"
     , sum(case when dt = '20110119' then 1 else 0 end) "c4"
     , sum(case when dt = '20110120' then 1 else 0 end) "c5"
     , sum(case when dt = '20110125' then 1 else 0 end) "c6"
     , sum(case when dt = '20110126' then 1 else 0 end) "c7"
     , sum(case when dt = '20110112' then 1 else 0 end)
     + sum(case when dt = '20110117' then 1 else 0 end)
     + sum(case when dt = '20110118' then 1 else 0 end)
     + sum(case when dt = '20110119' then 1 else 0 end)
     + sum(case when dt = '20110120' then 1 else 0 end)
     + sum(case when dt = '20110125' then 1 else 0 end)
     + sum(case when dt = '20110126' then 1 else 0 end) "tot"
from ttt
group by tech, nm
order by tech, nm;

-- grouping sets
select case when tech is null then 'TOTAL' else tech end "tech"
     , case when nm is null then 'SUM' else nm end "nm"
     , sum(case when dt = '20110112' then 1 else 0 end) "c1"
     , sum(case when dt = '20110117' then 1 else 0 end) "c2"
     , sum(case when dt = '20110118' then 1 else 0 end) "c3"
     , sum(case when dt = '20110119' then 1 else 0 end) "c4"
     , sum(case when dt = '20110120' then 1 else 0 end) "c5"
     , sum(case when dt = '20110125' then 1 else 0 end) "c6"
     , sum(case when dt = '20110126' then 1 else 0 end) "c7"
     , sum(case when dt = '20110112' then 1 else 0 end)
     + sum(case when dt = '20110117' then 1 else 0 end)
     + sum(case when dt = '20110118' then 1 else 0 end)
     + sum(case when dt = '20110119' then 1 else 0 end)
     + sum(case when dt = '20110120' then 1 else 0 end)
     + sum(case when dt = '20110125' then 1 else 0 end)
     + sum(case when dt = '20110126' then 1 else 0 end) "tot"
from ttt
group by grouping sets((tech), (tech, nm), ())
order by tech, nm;

/*********************
-- 동적쿼리
*********************/
-- 일자 개수 출력(7개)
-- 컬럼을 c1, c2, ... , c7으로 정의
select count(*)
from (select dt
      from ttt
      group by dt
     ) a;

-- 커서 확인
select 'c' || to_char(row_number() over(order by dt)) col_nm
     , dt
from ttt
group by dt;

-- 함수 생성
create or replace function fn_get_query
return varchar2
authid current_user
as
    iQuery varchar2(4000);
    iRowcnt number;
        
    cursor cur_getdt
    is
        select row_number() over(order by dt) seq
             , dt
        from ttt
        group by dt;
begin
    select count(*) into iRowcnt
    from (select dt
          from ttt
          group by dt
         ) a;
          
    iQuery := 'select case when tech is null then ''총합계'' else tech end "기술분야" '
           || '     , case when nm is null then '' '' else nm end "작업자" ';
                     
--    open cur_getdt;
--   
--    loop
--    fetch cur_getdt into iCol_nm, iDt;
--        exit when cur_getdt%notfound;
--       
--        iQuery := iQuery || ' , sum(case when dt = ''' || iDt || ''' then 1 else 0 end) "' || iCol_nm || '"';
--    end loop;
--   
--    close cur_getdt;    
        
    for i in cur_getdt loop
        iQuery := iQuery || ' , sum(case when dt = ''' || i.dt || ''' then 1 else 0 end) "' || i.dt || '"';
    end loop;
   
    for j in cur_getdt loop
        if j.seq = 1 then
            iquery := iquery || ' , sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) ';
        elsif  j.seq = iRowcnt then
            iquery := iquery || ' + sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) "총합계" ';
        else
            iquery := iquery || ' + sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) ';
        end if;
    end loop;
        
    iQuery := iQuery || 'from ttt '
                     || 'group by grouping sets((tech), (tech, nm), ()) '
                     || 'order by tech, nm; ';

    return iQuery;    
               
exception when others then
    return null;

end;

-- 함수 결과FN_GET_QUERY
select fn_get_query from dual;

-- 결과 값 실행 확인(WEB단 실행)
select case when tech is null then '총합계' else tech end "기술분야"
     , case when nm is null then ' ' else nm end "작업자" 
     , sum(case when dt = '20110112' then 1 else 0 end) "20110112"
     , sum(case when dt = '20110117' then 1 else 0 end) "20110117"
     , sum(case when dt = '20110118' then 1 else 0 end) "20110118"
     , sum(case when dt = '20110119' then 1 else 0 end) "20110119"
     , sum(case when dt = '20110120' then 1 else 0 end) "20110120"
     , sum(case when dt = '20110125' then 1 else 0 end) "20110125"
     , sum(case when dt = '20110126' then 1 else 0 end) "20110126"
     , sum(case when dt = '20110112' then 1 else 0 end) 
     + sum(case when dt = '20110117' then 1 else 0 end) 
     + sum(case when dt = '20110118' then 1 else 0 end)
     + sum(case when dt = '20110119' then 1 else 0 end) 
     + sum(case when dt = '20110120' then 1 else 0 end) 
     + sum(case when dt = '20110125' then 1 else 0 end) 
     + sum(case when dt = '20110126' then 1 else 0 end) "총합계"
from ttt
group by grouping sets((tech), (tech, nm), ())
order by tech, nm;
 
 
 
반응형

+ Recent posts