Last post Apr 07, 2017 03:29 AM by Lannie
Apr 06, 2017 05:19 AM|aoshi_kh|LINK
Hi, I currently will select daily data from time and qty. I perform group by time already.
May I know how can I display the output by showing time as column header and qty as row? And for any missing time, will need to set default as 0 qty on it.
1am 2am 3am 4am 5am ...
10 9 10 0 4
Currently my outcome like below
Apr 06, 2017 10:38 PM|Lannie|LINK
If you are using Oracle, you can use PIVOT command to PIVOT your vertical raw data into a spreadsheet looking data grid.
Apr 07, 2017 03:29 AM|Lannie|LINK
/* 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 */
'T_'||TO_CHAR(TO_DATE(TIME,'HH24'),'HH24') AS TIME, -- becomes your header name in pivot
NVL(QTY,0) AS QTY
WHERE DATE_TIME = SYSDATE - 1 -- show time from yesterday
MAX(TIME) FOR TIME IN ('T_0000','T_0100','T_0200','T_0300','T_0400','T_0500',
ORDER BY TIME