分析函数六：行列转换Pviot

关天ORACLE分析函数和行列转换通用包程序

1:介绍关于行列转换前的通用知识
Pivot查询是这样的，当你想要取出一些如下的数据时：
C1        C2        C3
――――――――――――
a1         b1        x1
a1         b1        x2
a1         b1        x3

想按如下格式显示：
C1      C2     C3(1)     C3(2)     C3(3)
--------------------------------------------------------
A1     b1      x1          x2       x3

这样就将行转变成了列。
2：

   在我们的日常开发中，经常会遇到这样的情况，就是分类汇总，而用一般的SQL写出的程序，数据是向行的方向展开的，但大部分报表的设计是希望向列的方向展开，这样的话，行列转换是非常必要和有用的！


测试数据：
SQL> select * from emp order by deptno, job;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10
      7782 CLARK      MANAGER         7839 09-6月 -81       2450                    10
      7839 KING       PRESIDENT            17-11月-81       5000                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87       3000                    20
      7902 FORD       ANALYST         7566 03-12月-81       3000                    20
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20
      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7900 JAMES      CLERK           7698 03-12月-81         99                    30
      7698 BLAKE      MANAGER         7839 01-5月 -81         99                    30
      7499 ALLEN      SALESMAN        7698 20-2月 -81         99        300         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81         99       1400         30
      7844 TURNER     SALESMAN        7698 08-9月 -81         99          0         30
      7521 WARD       SALESMAN        7698 22-2月 -81         99        500         30
        20                                 26-8月 -02


SQL> variable x refcursor
SQL> set autoprint on
SQL> begin
  2  my_pkg.pivot
  3  (p_max_cols_query => 'select max(count(*)) from emp
  4                        group by deptno,job',
  5   p_query => 'select deptno, job, ename, sal,
  6               row_number() over (partition by deptno, job
  7                      order by sal, ename)
  8               rn from emp a',
  9
 10     p_anchor => my_pkg.array('DEPTNO','JOB'),
 11     p_pivot  => my_pkg.array('ENAME', 'SAL'),
 12     p_cursor => :x );
 13  end;
 14  /

PL/SQL 过程已成功完成。


    DEPTNO JOB       ENAME_1         SAL_1 ENAME_2         SAL_2 ENAME_3         SAL_3 ENAME_4         SAL_4
---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

        10 CLERK     MILLER           1300
        10 MANAGER   CLARK            2450
        10 PRESIDENT KING             5000
        20 CLERK     SMITH             800 ADAMS            1100
        20 ANALYST   FORD             3000 SCOTT            3000
        20 MANAGER   JONES            2975
        30 CLERK     JAMES              99
        30 MANAGER   BLAKE              99
        30 SALESMAN  ALLEN              99 MARTIN             99 TURNER             99 WARD               99



SQL> begin
  2      my_pkg.pivot
  3      ( p_max_cols_query => 'select max(count(*)) from emp group by mgr',
  4        p_query => 'select a.ename mgr, b.ename,
  5                           row_number() over ( partition by a.ename order by b.ename ) rn
  6                      from emp a, emp b
  7                     where a.empno = b.mgr',
  8        p_anchor => my_pkg.array( 'MGR' ),
  9        p_pivot  => my_pkg.array( 'ENAME' ),
 10        p_cursor => :x );
 11  end;
 12  /

PL/SQL 过程已成功完成。


MGR        ENAME_1    ENAME_2    ENAME_3    ENAME_4    ENAME_5
---------- ---------- ---------- ---------- ---------- ----------
BLAKE      ALLEN      JAMES      MARTIN     TURNER     WARD
CLARK      MILLER
FORD       SMITH
JONES      FORD       SCOTT
KING       BLAKE      CLARK      JONES
SCOTT      ADAMS

已选择6行。

SQL> begin
  2    my_pkg.pivot
  3    (p_max_cols => 4,
  4     p_query => 'select job, count(*) cnt, deptno,
  5                    row_number() over (partition by job order by deptno) rn
  6                    from emp
  7                    group by job, deptno',
  8     p_anchor => my_pkg.array('JOB'),
  9     p_pivot  => my_pkg.array('DEPTNO', 'CNT'),
 10     p_cursor => :x );
 11  end;
 12  /

PL/SQL 过程已成功完成。


JOB         DEPTNO_1      CNT_1   DEPTNO_2      CNT_2   DEPTNO_3      CNT_3   DEPTNO_4      CNT_4
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ANALYST           20          2
CLERK             10          1         20          2         30          1
MANAGER           10          1         20          1         30          1
PRESIDENT         10          1
SALESMAN          30          4
                              1

已选择6行。



----------------------------------------------------------------------------
Tom的包：
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;
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
            l_query := l_query ||
                'max(decode(rn,'||i||','||
                            p_pivot(j)||',null)) ' ||
                            p_pivot(j) || '_' || i || ',';
        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;
/