DECLARE
l_return_value VARCHAR2( 4000 ) DEFAULT NULL;
BEGIN
l_return_value :=
PK_CROSSTAB.PivotSQL('SELECT LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES','LAST_NAME','DEPARTMENT_ID','sum','SALARY');
RETURN l_return_value;
END;

DECLARE vTABLE VARCHAR2( 100 );
BEGIN 
vTABLE := 'EMP'; 
IF HTMLDB_COLLECTION.COLLECTION_EXISTS('ATD')
 THEN  HTMLDB_COLLECTION.DELETE_COLLECTION('ATD');
 END IF; 
HTMLDB_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B('ATD', 'SELECT EMPNO, ENAME FROM '|| vTABLE ||' where EMPNO > 5000');END;

SELECT * FROM HTMLDB_COLLECTIONS WHERE COLLECTION_NAME = 'ATD'

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 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;