You can use DBMS_XMLGEN.GETXML to generate dynamic columns in a single SQL statement:
--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'
);
See this db<>fiddle for a runnable example.
A few notes about this solution:
- This XML trick only works when you have a set number of dynamic columns.
- You want to avoid this kind of dynamic solution when possible. Dynamic SQL in SQL is sometimes unavoidable, but usually there is a way to improve your data model to make the SQL simpler. Or perhaps this type of dynamic code is best handled by an application.
- As Thomas Kirchhoff pointed out, the solution requires some way of knowing which column comes first so I added a
COLUMN_ORDER value.
- Compared to Thomas' solution, my solution is a bit smaller and does not require creating additional objects. On the other hand, my solution is more cryptic will run slower. Which answer is best probably depends on how often you will use these queries. If this is a rare query, it's simpler to just use DBMS_XMLGEN and get it done. If this is a common query, it's worth creating and maintaining a package to simplify the query.