10 Replies Latest reply: Jan 29, 2013 1:36 AM by 986580 RSS

    Really working Crosstab / Pivot Report example

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