This discussion is archived
10 Replies Latest reply: Jan 28, 2013 11:36 PM by 986580 RSS

Really working Crosstab / Pivot Report example

654078 Newbie
Currently Being Moderated
try it here:
http://apex.oracle.com/pls/otn/f?p=20819:2

Details:

Create Report - based on PL/SQL ************************************

DECLARE
l_return_value VARCHAR2(32000) DEFAULT NULL;
BEGIN

l_return_value := PK_PIVOT.PivotSQL('SELECT * FROM TESTDATA', 'country,city,product','FDate', 'SUM', 'amount', nvl(:P2_PAGENUMBER,1) );
--:P2_PAGENUMBER - Textbox above the report

RETURN l_return_value;
END;

Create Supporting PACKAGE PK_PIVOT ************************************


create or replace
PACKAGE PK_PIVOT
--AUTHID CURRENT_USER
as
--code based on famous Tom Kyte's books examples
--but not copy-pasted from there

type refcursor is ref cursor;

type array is table of varchar2(30);

type array_varchar2 is table of varchar2(255);

Function PivotSQL (
p_query in varchar2, --query string which returns data you want to make crosstab on
p_rowfields in varchar2, --row fields separated by comma
p_columnfield in varchar2, --one column field
p_function in varchar2,--aggregate function ('SUM','AVG','COUNT','MIN','MAX')
p_functionfield in varchar2 --field for aggregate function
, p_page in number default 1--page from right to left (not all columns can be shown on one page)
) return varchar2; --returns query text for crosstab


/*
example:
SELECT PK_CROSSTAB.PivotSQL('SELECT * FROM scott.emp','empno','job','sum','sal') FROM SYS.DUAL

--------
SELECT deptno
,sum(DECODE(job,'BOSS',sal,null)) as BOSS
,sum(DECODE(job,'FIN',sal,null)) as FIN
,sum(DECODE(job,'HR',sal,null)) as HR
,sum(DECODE(job,'Sales',sal,null)) as Sales
FROM (SELECT * FROM scott.emp)
GROUP BY deptno
ORDER BY deptno
*/

end;




create or replace PACKAGE BODY PK_PIVOT as

Procedure FormatParam (var_data in varchar2, var_type in number, out_decode in out varchar2, out_col in out varchar2);


Function PivotSQL (
p_query in varchar2,--
p_rowfields in varchar2,
p_columnfield in varchar2,
p_function in varchar2,
p_functionfield in varchar2,
p_page in number default 1
) return varchar2
as
l_max_cols number;
l_query long;
l_columnnames array_varchar2 :=array_varchar2();
l_cursor refcursor;
tmp long;
--dbms_sql types:
l_theCursor integer default dbms_sql.open_cursor;--get col types
l_colCnt number default 0;
l_descTbl dbms_sql.desc_tab;
col_num number;
l_columnfieldtype number;
--decode names   
o_decode varchar2(50);
o_col varchar2(50);
l_cols_per_page number := 50;
l_begcol number;
l_endcol number;

begin
--check params
IF instr(p_columnfield,',')>0 THEN
raise_application_error (-20001, 'Can use only 1 columnfield');
ELSIF upper(p_function) not in ('SUM','AVG','COUNT','MIN','MAX') THEN
raise_application_error (-20001, 'Can use only standard aggregate functions');
END IF;

/* analyse query */
dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
/* get described columns for analysed query */
dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);

/* Tom Kyte:
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := l_descTbl.first;
loop
exit when (col_num is null);
--find column field type
if l_descTbl(col_num).col_name=upper(p_columnfield) then
l_columnfieldtype:=l_descTbl(col_num).col_type;
--dbms_output.put_line('Col#:'||col_num||' Name:'||l_descTbl(col_num).col_name||' Type:'||l_descTbl(col_num).col_type);
end if;

col_num := l_descTbl.next(col_num);
end loop;

--return 'test ok';

-- figure out the column names we must support for horizontal cross
if (p_columnfield is not null) then
tmp:='SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ') ORDER BY ' || p_columnfield;
-- dbms_output.put_line('columns cursor:'||tmp);
OPEN l_cursor for tmp;
LOOP
l_columnnames.EXTEND;
FETCH l_cursor into l_columnnames(l_columnnames.COUNT);
--dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
CLOSE l_cursor;

-- execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')' bulk collect into l_columnnames ;

else
raise_application_error (-20001, 'Cannot figure out max cols');
end if;

-- Now, construct the query that can answer the question for us...
l_query := 'SELECT ' || p_rowfields ;


l_begcol:=l_cols_per_page*(p_page-1)+1;
l_endcol:=l_cols_per_page*p_page;

if l_begcol>l_columnnames.count-1 then
l_begcol := l_columnnames.count-1;
end if;

if l_endcol>l_columnnames.count-1 then
l_endcol := l_columnnames.count-1;
end if;

--for i in  1 .. l_columnnames.count-1 loop
for i in l_begcol..l_endcol loop
FormatParam(l_columnnames(i),l_columnfieldtype, o_decode, o_col);--format params
l_query := l_query || ',' || p_function || '(DECODE(' || p_columnfield || ',' || o_decode || ','|| p_functionfield ||',null)) as "'|| o_col ||'" ' ; --" для строк с пробелами
end loop;

l_query := l_query || ' FROM (' || p_query || ')';

l_query := l_query || ' GROUP BY ' || p_rowfields || ' ORDER BY ' || p_rowfields;


/* close cursor */
dbms_sql.close_cursor(l_theCursor);

return l_query;

EXCEPTION
WHEN OTHERS THEN
/* close cursor */
dbms_sql.close_cursor(l_theCursor);
raise_application_error (-20001,'Error in PivotSQL:' || SQLERRM);
end;

--=========================

Procedure FormatParam (var_data in varchar2, var_type in number, out_decode in out varchar2, out_col in out varchar2)
--format parameter based on its type - for PivotSQL
--get parameter and its type
-- return strings for decode function and column name
/* dbms_sql.describe_columns types:
DATE Type:12
Varchar2 Type:1
Number Type:2
*/
IS

BEGIN

IF var_data is null THEN
out_decode:='NULL';
out_col:='==NULL==';


ELSIF var_type = 1 THEN -- Varchar2
out_decode:=''''||var_data||'''';--add quotes
out_col:=substr(var_data,1,30);

ELSIF var_type = 2 THEN --Number
out_decode:=var_data;--do nothing
out_col:=substr(var_data,1,30);

ELSIF var_type = 12 THEN --DATE
out_decode:='to_date('''||var_data||''')';--format as internal date
out_col:=to_char(to_date(var_data),'YYYY-MM-DD');

ELSE
out_decode:='== UNDEFINED TYPE:'||var_type;
out_col:='== UNDEFINED TYPE';
END IF;


EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20001,'Error in FormatParam:' || SQLERRM);

END;


end;