PHP Code:
create or replace package my_pkg
as
type refcursor is ref cursor;
type array is table of varchar2(30);
procedure pivot( p_max_cols in number default NULL,
p_max_cols_query in varchar2 default NULL,
p_query in varchar2,
p_anchor in array,
p_pivot in array,
p_cursor in out refcursor );
end;
/
create or replace package body my_pkg
as
procedure pivot( p_max_cols in number default NULL,
p_max_cols_query in varchar2 default NULL,
p_query in varchar2,
p_anchor in array,
p_pivot in array,
p_cursor in out refcursor )
as
l_max_cols number;
l_query long;
l_cnames array;
title_query varchar2(500);
month_year varchar2(30);
begin
/* figure out the number of columns we must support */
/* we either KNOW this or we have a query that can tell us */
if ( p_max_cols is not null )
then
l_max_cols := p_max_cols;
elsif ( p_max_cols_query is not null )
then
execute immediate p_max_cols_query into l_max_cols;
else
raise_application_error(-20001, 'Cannot figure out max cols');
end if;
/* Now, construct the query that can answer the question for us */
/* start with the C1, C2, ... CX columns: */
l_query := 'select ';
for i in 1 .. p_anchor.count
loop
l_query := l_query || p_anchor(i) || ',';
end loop;
/* Now add in the C{x+1}... CN columns to be pivoted: */
/* the format is "max(decode(rn,1,C{X+1},null)) cx+1_1" */
for i in 1 .. l_max_cols
loop
for j in 1 .. p_pivot.count
loop
title_query := 'select month_year from ('||p_query||') where rn = '||i;
execute immediate title_query into month_year;
l_query := l_query ||
'max(decode(rn,'||i||','||
p_pivot(j)||',null)) "' ||
month_year || '",';
end loop;
end loop;
/* Now just add in the original query */
l_query := rtrim(l_query,',')||' from ( '||p_query||') group by ';
/* and then the group by columns... */
for i in 1 .. p_anchor.count
loop
l_query := l_query || p_anchor(i) || ',';
end loop;
l_query := rtrim(l_query,',');
/* and return it */
execute immediate 'alter session set cursor_sharing=force';
open p_cursor for l_query;
execute immediate 'alter session set cursor_sharing=exact';
end;
end;
/
/*--------------------------*/
/* now run this in sqlplus */
/*--------------------------*/
variable x refcursor
set autoprint on
begin
my_pkg.pivot
(p_max_cols_query => 'select max(rn) from (
select row_number() over (
order by data_type_sid,to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY'') ) rn
from tattribute where entered_datetime < sysdate
group by data_type_sid, to_char(entered_datetime, ''MON-YYYY''))',
p_query => 'select * from (select data_type_sid,
to_char(entered_datetime, ''MON-YYYY'') month_year,
count(*) total,
row_number() over (order by data_type_sid,to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY'') ) rn
from tattribute
where entered_datetime < sysdate
group by data_type_sid, to_char(entered_datetime, ''MON-YYYY'')
order by to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY''))',
p_anchor => my_pkg.array( 'DATA_TYPE_SID' ),
p_pivot => my_pkg.array( 'TOTAL' ),
p_cursor => :x );
end;
/
PL/SQL procedure successfully completed.
DATA_TYPE_SID FEB-2004_1 JUN-2002_2 APR-2004_3 JUN-2002_4 AUG-2002_5 FEB-2003_6 APR-2003_7 AUG-2003_8 DEC-2003_9 FEB-2004_10 MAR-2004_11 APR-2004_12 MAY-2004_13 JUL-2004_14 JUL-2003_15
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 2
2 12 4
8 25 3 1 1 1 1 20 3 2 1 5
10 1
Elapsed: 00:00:00.00
Reply With Quote