Use output in select statement

Question

I have a case where I have to choose 100 columns out of 240 columns in select statement for that I have used below query to get those 100 column but couldn't use them in select statement

Query:

select listagg(column_name,',') within group (order by column_name) as col_name
from all_tab_cols
where lower(column_name) like 'test%'

Result:

col_name
-----------------------------    
test1,test2,test3,....test100

Expected output:

use those resulted values in select statement

select test1,test2,test3.... test100 
from table;

Thanks in advance

... select 'select from ' || listagg(column_name,',') WITHIN GROUP (ORDER BY column_name) || ' from table' as qry ... did You mean this? Then use the output of query in next query. E.g. using execute immediate. — pmi
– pmi, Commented Jun 29, 2017 at 6:43
Thank you, yes I'm expecting the same but don't know how to use execute immediate — Rajesh
– Rajesh, Commented Jun 29, 2017 at 6:51
Well, there is certainly some app you want to run this from. So use your query, build your final query string from its result and execute it. Where is the problem in that? — Thorsten Kettner
– Thorsten Kettner, Commented Jun 29, 2017 at 7:08

Avi · Accepted Answer · 2017-06-29 06:51:10Z

1

Dynamic query is what you wanted here. You can use below two query inside Stored Procedure or Functions.

select listagg(column_name,',') WITHIN GROUP (ORDER BY column_name) as col_name INTO VAR_COL_DETAILS   
from all_tab_cols    
where lower(column_name) like 'test%'

execute IMMIDEATE 'SELECT '||VAR_COL_DETAILS|| 'FROM TABLE_NAME';

answered Jun 29, 2017 at 6:51

Avi

1,1558 gold badges21 silver badges30 bronze badges

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

5 Comments

Rajesh Over a year ago

Thank you, but got an error while executing above query Error : missing keyword

Rajesh Over a year ago

I have used below procedure, but ended up with errors, create procedure test_col declare @VAR_COL_DETAILS; begin select listagg(column_name,',') WITHIN GROUP (ORDER BY column_name) as col_name INTO @VAR_COL_DETAILS from all_tab_cols where lower(column_name) like 'test%' execute IMMIDEATE 'SELECT '||VAR_COL_DETAILS|| 'FROM TABLE_NAME'; end ;

Avi Over a year ago

you need to use this query to output as cursor or string.. that depends on you. And the error message is because of missing semi colon(;) after 'test%'.

Avi Over a year ago

you are using mysql or Oracle?

Rajesh Over a year ago

I'm using Oracle

Collectives™ on Stack Overflow

Use output in select statement

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