Select configurable column names in Oracle SQL-CodePudding

I have a table with configurable column names which I can query like this:

SELECT
    column_name
FROM
    my_config
WHERE
    condition = 'MY_CONFIG_COLUMNS'

This is how 'my_config' tables looks like.

COLUMN_NAME              CONDITION
----------------------------------
    COLUMN1      MY_CONFIG_COLUMNS
    COLUMN2      MY_CONFIG_COLUMNS
    COLUMN3  NOT_MY_CONFIG_COLUMNS
    COLUMN4  NOT_MY_CONFIG_COLUMNS

My other table would look like this:

ID  COLUMN1 COLUMN2 COLUMN3 COLUMN4
ID1       X                       X
ID2       X       X       X       X
ID3       X               X

However, I would be looking to select only those columns that the query above returns in my other table, kind of like the below:

SELECT
    the_columns_in_the config
FROM
    my_other_table;

How could I achieve this?

Expected result:

COLUMN1 COLUMN2
---------------
      X 
      X       X
      X

CodePudding user response：

That would be a join between these two tables (at least, that's how I understood the question; it would help if you posted sample data and desired result).

select a.column_name
from my_config a join my_other_table b on b.the_column_in_the_config = a.column_name
where a.condition = 'MY_CONFIG_COLUMNS';

CodePudding user response：

You will need a dynamic query for the list of columns. It can be something like this:

DECLARE 
  l_column_list VARCHAR2(200);
  l_query       VARCHAR2(300);
BEGIN
  SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY column_name)
    INTO l_column_list
    FROM my_config
   WHERE condition = 'MY_CONFIG_COLUMNS';
   
  l_query := 'SELECT '||l_column_list||' FROM my_other_table';
  EXECUTE IMMEDIATE l_query;
  --do whatever you ant with the results
END;
/