Oracle SQL dynamically select column name

Question

A user gave me a table that looks like the following.

Name   HH08   HH09   HH10   HH11   HH12   HH13
Bob      2      3      4      2      7      1
Steve    2      9      3      2      2      5
Mike     2      2      2      2      3      2
Pat      1      0      2      0      0      0

I need some sql that will select the row based on the name, and the column based on the current hour of sysdate when the query is run.

If it is 9:27 am and the user is Steve, the sql needs to select the 9 value.

Is there any simple sql that will do this, or do I need to restructure the table the user gives me, which will be occassionally.

Thanks in advance.

Could always do it in a large case statement - not particularily nice but it would work — Andrew
– Andrew, Commented Jul 16, 2013 at 15:22
You should really re-structure the table, have it unique on name and hour rather than name alone. — Ben
– Ben, Commented Jul 16, 2013 at 15:24

user359040 · Accepted Answer · 2013-07-16 15:37:46Z

5

Try:

select case to_char(sysdate,'hh24')
           when '08' then hh08
           when '09' then hh09
           when '10' then hh10
           when '11' then hh11
           when '12' then hh12
           when '13' then hh13
       end OutputValue
from TableName
WHERE Name = 'Steve'

answered Jul 16, 2013 at 15:37

user359040

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

Comments

tkendrick20 · Accepted Answer · 2013-07-16 15:29:22Z

1

SELECT 'HH'+convert(char(2),DATEPART(hour,getdate()))
FROM TableName
WHERE Name = 'Steve'

try this out

answered Jul 16, 2013 at 15:29

tkendrick20

5201 gold badge4 silver badges14 bronze badges

1 Comment

Justin Cave Over a year ago

This appears to be SQL Server syntax, not Oracle. DATEPART, getdate and convert aren't valid in Oracle. And even if you convert the syntax to use the equivalent Oracle constructs, the SELECT statement won't dynamically change the column name it is fetching, it would just select a string literal which happened to also be the name of the column.

suPPLer · Accepted Answer · 2013-07-16 18:16:56Z

1

with t as (
  select 'Bob' name, 2 hh08, 3 hh09, 4 hh10, 2 hh11, 7 hh12, 1 hh13 from dual union all
  select 'Steve',    2,      9,      3,      2,      2,      5 from dual union all
  select 'Mike',     2,      2,      2,      2,      3,      2 from dual union all
  select 'Pat',      1,      0,      2,      0,      0,      0 from dual 
)
--/\-- Data sample --/\--
select value from t
  unpivot(value for hr in (hh08 as '08', hh09 as '09', hh10 as '10', hh11 as '11', hh12 as '12', hh13 as '13') )
 where hr = to_char(sysdate, 'HH24')
   and name = 'Pat';

answered Jul 16, 2013 at 18:16

suPPLer

4893 silver badges12 bronze badges

Collectives™ on Stack Overflow

Oracle SQL dynamically select column name

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related