Metaprogramming oracle sql select statement -

let's imagine have query following:

select   case when 1 = 1 1 else 0 end,   case when just_one = 1 1 else 0 end,   case when another_one = 1 1 else 0 end,    case when 2 = 1 1 else 0 end,   case when just_two = 1 1 else 0 end,   case when another_two = 1 1 else 0 end    -- 20 more things changes columns name   some_table; 

as can see, difference between these 2 groups in first 1 use columns have 'one' , in second ones have 'two' , have around 30 groups in actual query wonder if there way shorten somehow?

since different columns, must explicitly mention them separately in select list. cannot dynamically in pure sql.

i suggest, using text editor, hardly take minute or 2 write entire sql.

you use decode have less syntax instead of case expression verbose.

for example,

  decode(one, 1, 1, 0) col1,    decode(just_one, 1, 1, 0) col2,   decode(another_one, 1, 1, 0) col3,   decode(two, 1, 1, 0) col4,   decode(just_two, 1, 1, 0) col5,   decode(another_two, 1, 1, 0) col6 

i suggest stick sql, , not use pl/sql. not same, different engines. pl --> procedural language.

but if insist, use cursor loop loop through columns in [dba|all|user]_tab_cols. use sys_refcursor see data. first have build dynamic sql.