User269602965 posted
/* raw time must be 0 to 23 to be converted to 24 hour clock */
/* oracle column name cannot start with number, so prepend 'T_' */
/* you will have to pre-fill missing hours with correct numbers 0 to 23 in creation time table for each DAY */
SELECT
*
FROM
(
SELECT
'T_'||TO_CHAR(TO_DATE(TIME,'HH24'),'HH24') AS TIME, -- becomes your header name in pivot
NVL(QTY,0) AS QTY
FROM
YOUR_TIME_TABLE
WHERE DATE_TIME = SYSDATE - 1 -- show time from yesterday
)
PIVOT
(
MAX(TIME) FOR TIME IN ('T_0000','T_0100','T_0200','T_0300','T_0400','T_0500',
'T_0600','T_0700','T_0800','T_0900','T_1000','T_1100',
'T_1200','T_1300','T_1400','T_1500','T_1600','T_1700',
'T_1800','T_1900','T_2000','T_2100','T_2200','T_2300')
)
ORDER BY TIME
/