create or replace package PKG_PIVOT is

 function pivot_sql (
                     p_max_cols_query in varchar2 default null
                    , p_query in varchar2
                    , p_anchor in varchar2_table
                    , p_pivot in varchar2_table
                    , p_pivot_head_sql in varchar2_table default varchar2_table()
                    ) 
 return varchar2;
 
 function pivot_ref (
                        p_max_cols_query in varchar2 default null
                     , p_query in varchar2
                     , p_anchor in varchar2_table
                     , p_pivot in varchar2_table
                     , p_pivot_name in varchar2_table default varchar2_table()
                     )
 return sys_refcursor;

end PKG_PIVOT;


create or replace package body PKG_PIVOT is
/**
* Function returning query
*/
 function pivot_sql (
                     p_max_cols_query in varchar2 default null
                    , p_query in varchar2
                    , p_anchor in varchar2_table
                    , p_pivot in varchar2_table
                    , p_pivot_head_sql in varchar2_table
                    ) return varchar2
                    is
    l_max_cols number;
    l_query varchar2(5000);
    l_pivot_name varchar2_table:=varchar2_table();
    k integer;
    c1 sys_refcursor;
    v varchar2(500);
 begin
    -- Получаем кол-во столбцов
    if (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;

    -- Собираем по кускам необходимый нам запрос
    l_query := 'select ';

    for i in 1 .. p_anchor.count loop
     l_query := l_query || p_anchor (i) || ',';
    end loop;
    --Получаем названия колонок
    k:=1;
    if p_pivot_head_sql.count=p_pivot.count
     then
         for j in 1 .. p_pivot.count loop
            open c1 for p_pivot_head_sql(j);
            loop
             fetch c1 into v;
             l_pivot_name.extend(1);
             l_pivot_name(k):=v;
             EXIT WHEN c1%NOTFOUND;
             k:=k+1;
            end loop;
         end loop;
    end if;
     
    -- Добавляем колонки с полученными названиями
    -- в виде "max(decode(rn,1,C{X+1},null)) c_name+1_1"
    for i in 1 .. l_max_cols loop
     for j in 1 .. p_pivot.count loop
        l_query := l_query || 'sum(decode(rn,' || i || ',' || p_pivot (j) || ',0)) '
                  ||'"' ||l_pivot_name ((j-1)*l_max_cols+i) ||'"'|| ',';
     end loop;
    end loop;

    -- Вставляем исходный запрос
    l_query := rtrim (l_query, ',') || ' from ( ' || p_query || ') group by ';

    -- Группируем по колонкам
    for i in 1 .. p_anchor.count loop
     l_query := l_query || p_anchor (i) || ',';
    end loop;

    l_query := rtrim (l_query, ',');

    -- Возвращаем готовый SQL запрос
    return l_query;
 end;
 
/**
* Функция возвращающая курсор на выполненный запрос
*/
 function pivot_ref (
                     p_max_cols_query in varchar2 default null
                    , p_query in varchar2
                    , p_anchor in varchar2_table
                    , p_pivot in varchar2_table
                    , p_pivot_name in varchar2_table
                    ) return sys_refcursor
                    is
    p_cursor sys_refcursor;
 begin
    execute immediate 'alter session set cursor_sharing=force';
    open p_cursor for pkg_pivot.pivot_sql (
                     p_max_cols_query
                    , p_query
                    , p_anchor
                    , p_pivot
                    , p_pivot_name
                    );
    execute immediate 'alter session set cursor_sharing=exact';
    return p_cursor;
 end;
end PKG_PIVOT;