locked
How to display oracle data into column RRS feed

  • 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

    Thursday, April 6, 2017 5:19 AM

Answers

  • 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
      /
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:29 AM

All replies

  • User269602965 posted

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

    Thursday, April 6, 2017 10:38 PM
  • 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
      /
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:29 AM