The xt_pivot's intro from xtender

xt_pivot's Introduction

Package for dynamic pivotting - Tom Kyte's package modification.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740#14642820392307

Example:

 begin
  :qq:=XT_PIVOT.pivot_sql(
                                     'select count(distinct trunc(dt)) from actions'
                                    , 'select e.name name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
                                    , varchar2_table('NAME')
                                    , varchar2_table('SUM_CNT')
                                    , varchar2_table('select distinct ''Date ''||trunc(dt) from actions')
                                );
  :qc :=XT_PIVOT.pivot_ref(
                                     'select count(distinct trunc(dt)) from actions'
                                    , 'select e.name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
                                    , varchar2_table('NAME')
                                    , varchar2_table('SUM_CNT')
                                    , varchar2_table('select distinct ''Date ''||trunc(dt) from actions')
                                );
 end;

Recommend Projects