CODEWITHSUNDEEP
sqloracle-databaseselect
Joe
Joe

Reputation: 201

Oracle SQL dynamically select column name

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.

Upvotes: 1

Views: 10347

Answers (3)

suPPLer
suPPLer

Reputation: 489

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

Upvotes: 1

user359040
user359040

Reputation:

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'

Upvotes: 5

tkendrick20
tkendrick20

Reputation: 520

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

try this out

Upvotes: 1

Related Questions