Skip to content
Advertisement

Oracle select column names dynamically from another table

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.

Advertisement

Answer

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
1 People found this is helpful
Advertisement