oracle - Generate Dynamic Column Value SQL

Question

I have two tables. I need to form a ratio based on the config table columns. Since 100 has only PROFIT and LOSS columns ratio would be PROFIT:LOSS. Is this require Dynamic SQL.

I tried to write but I am stuck not sure how to proceed. can you please suggest.

Config Table:

Table - A100

Output: I neeed to form a ratio based on the table 1 and table 2

Query I tried:

    WITH col as (SELECT  COLUMN_NAME
             FROM   config
             WHERE TABLE_NAME='A100' 
            ) ,
            
 fetch_col as (    SELECT DISTINCT CUSTOMER,COLUMN_NAME,SALES,PROFIT,LOSS,DIVIDEND
                   FROM A100 CROSS JOIN dba_tab_columns 
                   WHERE COLUM_NAME IN (SELECT COLUMN_NAME FROM col)    
            )
   SELECT  CUSTOMER,PROFI||':'||LOSS
            CASE WHEN a.COLUMN_NAME=b.COLUMN_NAME THEN PROFIT||':'||LOSS ELSE '' END         
   FROM fetch_col a INNER JOIN col b
   ON a.COLUMN_NAME=b.COLUMN_NAME

question from:https://stackoverflow.com/questions/65942344/generate-dynamic-column-value-sql

Answer 1

I have tried to solve your problem. Maybe it does not fit yet.

I have extended the table "config" by a column "column_type" to identify which entry represents numerator and which denominator.

CREATE OR REPLACE PACKAGE pkg_demo
AS
   TYPE r_out IS RECORD
      ( table_name   VARCHAR2(30),
        customer     VARCHAR2(30),
        ratio        VARCHAR2(30)
      );
   TYPE t_out IS TABLE OF r_out;

   FUNCTION get_ratio ( i_table  IN VARCHAR2 )
      RETURN t_out PIPELINED;
   
END pkg_demo;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo
AS

   FUNCTION get_ratio ( i_table  IN VARCHAR2 )
      RETURN t_out PIPELINED
   AS
      l_num config.column_name%TYPE;
      l_den config.column_name%TYPE;

      l_sql VARCHAR2(32767);
      TYPE cur_type IS REF CURSOR;
      l_cur cur_type;
      l_rec r_out;
   BEGIN
      SELECT config.column_name
        INTO l_num
        FROM config
       WHERE table_name = i_table
         AND column_type = 'N';
      
      SELECT config.column_name
        INTO l_den
        FROM config
       WHERE table_name = i_table
         AND column_type = 'D';

      l_sql := 'SELECT ''' || TRIM(LEADING 'A' FROM i_table) || ''', customer, TO_CHAR(' || l_num || ') ||'':''|| TO_CHAR('|| l_den || ', ''FM00'') FROM ' || i_table;
      
      OPEN l_cur FOR l_sql;
      LOOP
         FETCH l_cur INTO l_rec;
         EXIT WHEN l_cur%NOTFOUND;
         
         PIPE ROW(l_rec);
      END LOOP;
      
      CLOSE l_cur;
         
   END get_ratio;
   
END pkg_demo;
/

SELECT * FROM TABLE(pkg_demo.get_ratio('A100'));

Result:

TABLE_NAME  CUSTOMER  RATIO                         
----------- --------- ---------
100         Microsoft 10:01                         
100         Tesla     15:02