drop table test_tbl purge
/
create table test_tbl as (
                          select 'blue' val,1 rn,1 col from dual union all
                          select 'pink',1,2 from dual union all
                          select 'azure',1,3 from dual union all
                          select 'orange',2,1 from dual union all
                          select 'green',2,2 from dual
                         )
/

create or replace
  procedure create_pivot_test_tbl_ptt
    authid current_user
    is
        v_stmt clob := 'CREATE PRIVATE TEMPORARY TABLE ORA$PTT_PIVOT_TEST_TBL AS SELECT ';
        v_cnt  number;
    begin
        select  count(*)
          into  v_cnt
          from  user_private_temp_tables
          where table_name = 'ORA$PTT_PIVOT_TEST_TBL';
        if v_cnt = 1
          then
            execute immediate 'DROP TABLE ORA$PTT_PIVOT_TEST_TBL PURGE';
            commit; -- важно
        end if;
        select  max(col)
          into  v_cnt
          from  test_tbl;
        for v_i in 1..v_cnt loop
          v_stmt := v_stmt || 'MAX(CASE COL WHEN ' || v_i || ' THEN VAL END) COL' || v_i || ',';
        end loop;
        v_stmt := rtrim(v_stmt,',') || ' FROM TEST_TBL GROUP BY RN ORDER BY RN';
        execute immediate v_stmt;
end;
/

Procedure created.

SQL>

SQL> exec create_pivot_test_tbl_ptt

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  ora$ptt_pivot_test_tbl
  3  /

COL1   COL2   COL3
------ ------ ------
blue   pink   azure
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL> exec create_pivot_test_tbl_ptt

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  ora$ptt_pivot_test_tbl
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'teal',
  5           3,
  6           4
  7          )
  8  /

1 row created.

SQL> exec create_pivot_test_tbl_ptt

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  ora$ptt_pivot_test_tbl
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL>

drop table test_tbl purge
/
drop sequence test_tbl_seq
/
create table test_tbl as (
                          select 'blue' val,1 rn,1 col from dual union all
                          select 'pink',1,2 from dual union all
                          select 'azure',1,3 from dual union all
                          select 'orange',2,1 from dual union all
                          select 'green',2,2 from dual
                         )
/
create or replace
  function pivot_test_tbl_macro
    return varchar2
    sql_macro
    is
        v_stmt clob := 'SELECT ';
        v_cnt  number;
    begin
        select  max(col)
          into  v_cnt
          from  test_tbl;
        for v_i in 1..v_cnt loop
          v_stmt := v_stmt || 'MAX(CASE COL WHEN ' || v_i || ' THEN VAL END) COL' || v_i || ',';
        end loop;
        v_stmt := rtrim(v_stmt,',') || ' FROM TEST_TBL GROUP BY RN ORDER BY RN';
        execute immediate v_stmt;
        return v_stmt;
end;
/
create sequence test_tbl_seq -- для форсирования hard parse
/

SQL> set verify off
SQL> column test_tbl_seq new_value test_tbl_seq noprint
SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3
------ ------ ------
blue   pink   azure
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'teal',
  5           3,
  6           4
  7          )
  8  /

1 row created.

SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL>

select  *
  from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
/

create or replace
  function pivot_test_tbl_macro
    return varchar2
    sql_macro
    is
        v_stmt clob := 'SELECT ';
        v_cnt  number;
    begin
        select  max(col)
          into  v_cnt
          from  test_tbl;
        for v_i in 1..v_cnt loop
          v_stmt := v_stmt || 'MAX(CASE COL WHEN ' || v_i || ' THEN VAL END) COL' || v_i || ',';
        end loop;
        v_stmt := rtrim(v_stmt,',') || ' FROM TEST_TBL GROUP BY RN ORDER BY RN';
        execute immediate v_stmt;
        return v_stmt;
end;
/

SQL> set verify off
SQL> column column_count new_value column_count noprint
SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3
------ ------ ------
blue   pink   azure
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'teal',
  5           3,
  6           4
  7          )
  8  /

1 row created.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL> delete test_tbl
  2   where col = 7
  3  /

1 row deleted.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL>

SQL> select  *
  2    from  table(pivot_test_tbl_macro)
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL>  -- не форсируем hard parse
SQL>  select  *
  2    from  table(pivot_test_tbl_macro)
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count 7 форсируем hard parse
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL>