I think the questioner wants one column per hour in the day so far. For example:
drop table t purge;
create table t as
select trunc(sysdate) + (level-1)/48 dte,
level num
from dual
connect by level <= 8;
declare
l_sqltext varchar2(4000) :=
'select * from (
select extract(hour from to_timestamp(dte)) hr, num
from t
where dte >= trunc(sysdate)
)
pivot(sum(num) for hr in(#PIVOTLIST#))';
l_pivotlist varchar2(4000);
begin
select listagg((level-1) || ' as "'||to_char(level-1,'fm09')||':00-'||to_char(level,'fm09')||':00"', ',')
within group(order by level)
into l_pivotlist
from dual
connect by level <= (
select count(distinct extract(hour from to_timestamp(dte)))
from t
where dte >= trunc(sysdate)
);
l_sqltext := replace(l_sqltext, '#PIVOTLIST#', l_pivotlist);
dbms_output.put_line(l_sqltext);
end;
/
select * from (
select extract(hour from to_timestamp(dte)) hr, num
from t
where dte >= trunc(sysdate)
)
pivot(sum(num) for hr in(0 as "00:00-01:00",1 as "01:00-02:00",2 as "02:00-03:00",3 as "03:00-04:00"));
00:00-01:00 01:00-02:00 02:00-03:00 03:00-04:00
----------- ----------- ----------- -----------
3 7 11 15
Best regards, Stew