By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table config as
select 'A100' table_name, 'PROFIT' column_name, 1 column_order from dual union all
select 'A100' table_name, 'LOSS' column_name, 2 column_order from dual;
2 rows affected
create table a100 as
select 'Microsoft' customer, 1000 sales, 10 profit, 1 loss, 6 dividend from dual union all
select 'Tesla' customer, 2000 sales, 15 profit, 2 loss, 3 dividend from dual;
2 rows affected
--Convert the XML to relational data.
select table_name, customer, ratio
from
(
--Convert the SQL statement into XML results.
select XMLType(dbms_xmlgen.getXML(sql)) results
from
(
--Generate a SQL statement with column names based on configuration.
select table_name,
replace(replace(replace(
q'[select '#TABLE_NAME#' table_name, customer, #COLUMN_1# || ':' || #COLUMN_2# ratio from #TABLE_NAME#]'
, '#TABLE_NAME#', table_name)
, '#COLUMN_1#', max(column_name) keep(dense_rank first order by column_order))
, '#COLUMN_2#', max(column_name) keep(dense_rank first order by column_order desc)) sql
from config
group by table_name
)
)
cross join
xmltable
(
'/ROWSET/ROW'
passing results
columns
table_name varchar2(128) path 'TABLE_NAME',
customer varchar2(128) path 'CUSTOMER',
ratio varchar2(128) path 'RATIO'
);
| TABLE_NAME | CUSTOMER | RATIO |
|---|---|---|
| A100 | Microsoft | 10:1 |
| A100 | Tesla | 15:2 |