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
Advertisement
Answer
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; /