Oracle pivot table with dynamic columns

53 Views Asked by At

I have 3 tables as below.

TABLE 1: COMPANY (contains company_id and company_name)

enter image description here

TABLE 2: CONFIGURATION (contains configuration_id and configuration_name)

enter image description here

TABLE 3: COMPANY_CONFIGURATION (Contains the configuration mapping for each company) enter image description here

I am trying to display the configurations of each company in a pivot (dynamic columns) as below.

enter image description here

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.

enter image description here

Questions

  1. What am I doing wrong here?
  2. How can I display the respective config_value under each company column?

Any help is highly appreciated!

0

There are 0 best solutions below