I have 3 tables as below.
TABLE 1: COMPANY (contains company_id and company_name)
TABLE 2: CONFIGURATION (contains configuration_id and configuration_name)
TABLE 3: COMPANY_CONFIGURATION (Contains the configuration mapping for each company)

I am trying to display the configurations of each company in a pivot (dynamic columns) as below.
Below is the script which I have written
DECLARE
columns_query VARCHAR2(32767);
query VARCHAR2(32767);
columns_list VARCHAR2(32767);
result_cursor SYS_REFCURSOR;
BEGIN
-- Generate the column list dynamically
SELECT LISTAGG('''' || company_name || '''', ', ') WITHIN GROUP (ORDER BY company_name)
INTO columns_list
FROM company;
-- Build the dynamic SQL query
columns_query := '
SELECT
configuration_id,
configuration_name,
' || columns_list || '
FROM (
SELECT
c.company_id,
c.company_name,
cn.configuration_id,
cn.configuration_name,
cc.config_value
FROM
company_configuration cc
JOIN
company c ON cc.company_id = c.company_id
JOIN
Configuration cn ON cc.configuration_id = cn.configuration_id
) src
PIVOT (
MAX(config_value)
FOR company_name IN (' || columns_list || ')
) pivoted';
-- Execute the dynamic SQL query
OPEN result_cursor FOR columns_query;
dbms_sql.return_result(result_cursor);
END;
/
However, in the script output, the company name is printed in all rows instead of the respective config_value. Please find the result below.
Questions
- What am I doing wrong here?
- How can I display the respective config_value under each company column?
Any help is highly appreciated!



