Selecting dynamic no. of columns using select Query in Oracle

Question

Suppose there is a table(TB_dynamic_date) with column_name as 'Data_to_select'

Lets say there is another table TB_Main_Data with column names as Name,DOJ,id

So , Is it possible to write a query in a way ,through which i can fetch dynamic no. of columns from TB_Main_Data,using the table TB_dynamic_date where TB_dynamic_data contains the following records under 'Data_to_select' as string

1)Name,DOJ
2)Name,id
3)Name,id,DOJ
4)Name,id,DOj,Name||id

What i need to do is something like the following:-

select **(1/2/3/4 record from TB_dynamic_data)** from TB_Main_Data

I Hope, i was able to explain clearly , what i want to ask.

You can do this with dynamic SQL (execute immediate) but not with a simple query. — Gordon Linoff
– Gordon Linoff, Commented Sep 20, 2014 at 12:56
Dynamic sql can be generated and executed only in PL/SQL. Write an anonymous block, prepare the query dynamically, concatenate it with the variables you declared and assigned for the column names or values. — Lalit Kumar B
– Lalit Kumar B, Commented Sep 20, 2014 at 14:00

Gordon Linoff · Accepted Answer · 2014-09-20 14:08:59Z

1

In Oracle, this would look like:

declare
    v_columns varchar(4000);
    v_sql varchar(4000);
begin
    select data_to_selecct
    into v_columns
    from tb_dynamic_data
    where <whatever>;

    v_sql := 'select ' || v_columns || ' from tb_main_data';
    execute immediate v_sql;
end;

answered Sep 20, 2014 at 14:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

radar · Accepted Answer · 2014-09-20 13:58:48Z

0

You can use dynamic query like below

Declare @cols= ( select Data_to_select from TB_Dynamic_data where Id=2)
Declare @query nvarchar(max)  
Set @query = ' select '  + @cols + ' from TB_main_data'

Exec(@query)

answered Sep 20, 2014 at 13:58

radar

13.4k2 gold badges27 silver badges33 bronze badges

Collectives™ on Stack Overflow

Selecting dynamic no. of columns using select Query in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related