Fetch data from one table columns whose names are value of another table

I need to fetch columns from a master table and the list of columns that i need to get will be in a metadata table for different users.This would need to be dynamic as each user might not have the same number of columns to be fetched.

Metadata table

User    short_desc
rk      sector
rk      Industry
pa      sector
pa      Industry
pa      Subindustry

Master Data table

ID          sector              Industry                  Subindustry
594918104   Technology          Information Technology    CyberSecurity
G0464B107   Financial Services  Financials                Banks

When user rk is selected from meta table ,i would want to get only sector and industry When user pa is selected i would want to get only sector ,industry and subindustry field also.

Tried this from one more solution,but this is not dynamic.

SELECT t2.user_id,
       t1.attr_name,
       CASE
          WHEN t1.attr_path = 'str1' THEN t2.str1
          WHEN t1.attr_path = 'str2' THEN t2.str2
       END
          col_sel
  FROM metadata t1 INNER JOIN class t2 ON t1.org_id = t2.org_id
 WHERE t1.org_id = 1

When user rk is selected from meta table ,i would want to get only sector and industry When user pa is selected i would want to get only sector ,industry and subindustry field also.

Answer

One option is to use a function that returns ref cursor.

Test case:

SQL> select * from metadata;

C_ SHORT_DESC
-- --------------------
rk sector
rk industry
pa industry
pa sector
pa subindustry

SQL> select * from master_data;

ID         SECTOR          INDUSTRY             SUBINDUSTRY
---------- --------------- -------------------- ---------------
5949       technology      information tech     cyber security
g046       financial       financials           banks

Function:

SQL> create or replace function f_meta (par_user in varchar2)
  2    return sys_refcursor
  3  is
  4    l_str varchar2(200);
  5    rc    sys_refcursor;
  6  begin
  7    select listagg(short_desc, ', ') within group (order by null)
  8      into l_str
  9      from metadata
 10      where c_user = par_user;
 11
 12    l_str := 'select ' || l_str ||' from master_data';
 13
 14    open rc for l_str;
 15    return rc;
 16  end;
 17  /

Function created.

Testing:

SQL> select f_meta('rk') from dual;

F_META('RK')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

INDUSTRY             SECTOR
-------------------- ---------------
information tech     technology
financials           financial


SQL> select f_meta('pa') from dual;

F_META('PA')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

INDUSTRY             SECTOR          SUBINDUSTRY
-------------------- --------------- ---------------
information tech     technology      cyber security
financials           financial       banks


SQL>