How to display oracle data into column

Question

User1248258412 posted

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.

eg:

1am  2am  3am   4am  5am ...

10     9        10      0        4

Currently my outcome like below

time   qty

1       10

17       0

2         9

3         10

5          4

Answer 1

User269602965 posted

If you are using Oracle, you can use PIVOT command to PIVOT your vertical raw data into a spreadsheet looking data grid.

Answer 2

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
  /