select columns by a concat text as columnname in oracle

Question

I have a table with columns named with the number of hour of day like this:

col00 NUMBER(5)
col01 NUMBER(5)
col02 NUMBER(5)
...
col23 NUMBER(5)

...and I have another query that returns a count by hour.

I want to recover the colXX value by hour.... then I can recover with "decode" or "case when..." but I want know if exists any way to recover the column by a text like this:

select "col"||hour from table;

in the hypothetical above example if hour is 13 then would be translated like:

select col13 from table;

there is any way to do this ?

Answer 1

You have to use dynamic SQL:

EXECUTE IMMEDIATE 'SELECT col'|| hour || ' FROM TABLE;'

Reference:

• EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL

Answer 2

While it looks a little...brute force...you could use a bunch of UNION statements between queries:

SELECT col00
  FROM TABLE 
 WHERE to_char(SYSDATE, 'HH24') = '00'
UNION
SELECT col01
  FROM TABLE 
 WHERE to_char(SYSDATE, 'HH24') = '01'
UNION
SELECT col02
  FROM TABLE 
 WHERE to_char(SYSDATE, 'HH24') = '02'  
   (...and so on...)
UNION
SELECT col23
  FROM TABLE 
 WHERE to_char(SYSDATE, 'HH24') = '23' 

Answer 3

Closing...

select REGEXP_REPLACE( REGEXP_REPLACE( DBMS_XMLGEN.GETXML(' SELECT col'|| to_char(sysdate,'HH24') || ' FROM TABLE '),'.<[/]?(\?xml .>|ROW).',''),' (<([^>])>([^<])]>.*)','\2=\3') AS RES from dual

its not so simple but very flexible and works great for me.