Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
select
  trunc(level/5) as id
  , chr(65+mod(level, 5)) as code
  , level as val
from dual
connect by level < 10

9 rows affected


create function f_pivot
return varchar2 SQL_MACRO(TABLE)
is
  l_codes varchar2(1000);
begin
  select listagg(
    distinct '''' || code
    || ''' as ' || code, ',')
    into l_codes
  from t;
  
  return
    'select *
    from t
    pivot (
      max(val) for code in (
      ' || l_codes || '))';
end;
/
select *
from f_pivot()


ID
B
C
D
E
A


0
1
2
3
4
null

1
6
7
8
9
5


insert into t
values(1, 'Q', 100)

1 rows affected


commit
select *
from f_pivot()


ID
B
C
D
E
A


0
1
2
3
4
null

1
6
7
8
9
5


select dbms_xmlgen.getxml('select * from f_pivot()') as v
from dual


V


<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>0</ID>
  <B>1</B>
  <C>2</C>
  <D>3</D>
  <E>4</E>
 </ROW>
 <ROW>
  <ID>1</ID>
  <B>6</B>
  <C>7</C>
  <D>8</D>
  <E>9</E>
  <A>5</A>
  <Q>100</Q>
 </ROW>
</ROWSET>



with function f_pivot1
return varchar2 SQL_MACRO(TABLE)
is
  l_codes varchar2(1000);
begin
  select listagg(distinct '''' || code || ''' as ' || code, ',')
    into l_codes
  from t;
  
  return
    'select *
    from t
    pivot (
      max(val) for code in (
      ' || l_codes || '))';
end;

select *
from f_pivot1()


ID
B
C
D
E
A
Q


0
1
2
3
4
null
null

1
6
7
8
9
5
100

ID	B	C	D	E	A
0	1	2	3	4	null
1	6	7	8	9	5

ID	B	C	D	E	A
0	1	2	3	4	null
1	6	7	8	9	5

ID	B	C	D	E	A	Q
0	1	2	3	4	null	null
1	6	7	8	9	5	100