CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Rajesh
Rajesh

Reputation: 215

Use output in select statement

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

Upvotes: 0

Views: 84

Answers (1)

Avi
Avi

Reputation: 1145

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';

Upvotes: 1

Related Questions