Oracle select column names dynamically from another table

Question

I have two tables. Lets say tab1 and tab2.

tab1 has data like this:

ID Field Name
00001 col1
00001 col63
00002 col3
00002 col47
00003 col22
00003 col40

Each ID has always 2 Field Name values.

And tab2 has columns col1,col2,col3,col4....col70

I want to select columns dynamically from tab2.

Like for 00001 it should be:

select col1,col63 from tab2;

Similarly for 00002 it should be :

select col3,col47 from tab2;

Is there any way to achieve this without using function or procedure ? I am using Oracle 11G.

case field_name when 'col1' then col1 when 'col2' then col2.... Or in Oracle fashion: decode(field_name, 'col1', col1, 'col2', col2, 'col3', col3) no dynamic SQL and its vulnerability — astentx
– astentx, Commented Jul 28, 2021 at 20:55

Gordon Linoff · Accepted Answer · 2021-07-28 20:54:25Z

2

Assuming that the columns have compatible types, then you can do what you want by using case expressions:

select (case when t1.field1 = 'col1' then col1
             when t1.field1 = 'col2' then col2
             when t1.field1 = 'col3' then col3
             when t1.field1 = 'col4' then col4
        end),
       (case when t1.field2 = 'col1' then col1
             when t1.field2 = 'col2' then col2
             when t1.field2 = 'col3' then col3
             when t1.field2 = 'col4' then col4
        end)
from tab2 join
     (select id, min(fieldname) as field1, max(fieldname) as field2
      from t1
      group by id
     ) t1
     on t1.id = t2.id

answered Jul 28, 2021 at 20:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sayandip Ghatak Over a year ago

the solution is great. but one thing , the tab2 has at least 70 columns, and the field name of tab1 may vary from col1 to col70.

MT0 Over a year ago

@SayandipGhatak Then write 70 terms in the CASE statement.

Sayandip Ghatak Over a year ago

Isn't there any solution like this : select (select listagg(fieldname,',') from tab1 where id = '00001') from tab2

astentx Over a year ago

@SayandipGhatak SQL is a static language. Optimizer needs to built the execution plan, obviously, before the execution. How can it do this without column names?

Gordon Linoff Over a year ago

@SayandipGhatak . . . You can generate the code using a SQL query or spreadsheet.

Collectives™ on Stack Overflow

Oracle select column names dynamically from another table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related