This discussion is archived
9 Replies Latest reply: Jun 20, 2013 5:59 AM by perforsgren RSS

Crosstab

perforsgren Newbie
Currently Being Moderated

Oracle 11.2

 

I have a table t with these columns:

POSITION    NUMBER(5,0)    Not null

DATE_ENTRY    DATE    Not null

 

I want to create a hourly count of all rows in the table for a certain position. All hours should be represented in the table even if there are no rows for that hour.

 

HOUR   TODAY   DAY -1  DAY -2  DAY -3   DAY -4  DAY -5  DAY -6       <== Column can also be presented as a date "YYYY-MM-DD" but should always contain 7 days.

00   

01              22          18        16         23          23                 12

02

03            311          47         33

..

22                                        66                              70

23

 

 

To be able to have the statement selectable on different POSITION, my intention is to create a view for this (POSITION not shown in the crosstab example).

I can produce the figures for ONE day, but not for multiple days according to the example.

Any ideas how to do this efficiently?

 

Best regards

/Per

  • 1. Re: Crosstab
    Hoek Guru
    Currently Being Moderated

    You can generate the missing rows using connect by and level.

    Here's a simple example:

    SQL> with hours as ( select to_char(trunc(sysdate)+level/24, 'hh24') hr
      2                  from   dual
      3                  connect by level <= 24
      4                )
      5  ,    weekdays as ( select to_char(trunc(sysdate, 'iw')-level, 'dy') dy
      6                     ,      trunc(sysdate, 'iw')-level dt
      7                     from   dual
      8                     connect by level <= 7
      9                   )
     10  select hr
     11  ,      max(case when dy = 'mon' then dt end) mon
     12  ,      max(case when dy = 'tue' then dt end) tue
     13  ,      max(case when dy = 'wed' then dt end) wed 
     14  ,      max(case when dy = 'thu' then dt end) thu
     15  ,      max(case when dy = 'fri' then dt end) fri
     16  ,      max(case when dy = 'sat' then dt end) sat
     17  ,      max(case when dy = 'sun' then dt end) sun
     18  from   hours
     19  ,      weekdays
     20  group by hr
     21  order by hr;
    HR MON                 TUE                 WED                 THU                 FRI                 SAT                 SUN
    -- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
    00 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    01 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    02 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    03 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    04 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    05 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    06 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    07 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    08 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    09 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    10 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    11 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    12 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    13 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    14 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    15 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    16 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    17 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    18 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    19 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    20 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    21 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    22 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    23 10-06-2013 00:00:00 11-06-2013 00:00:00 12-06-2013 00:00:00 13-06-2013 00:00:00 14-06-2013 00:00:00 15-06-2013 00:00:00 16-06-2013 00:00:00
    24 rows selected.
    SQL>

     

    On 11.2 you could use the PIVOT operator as well, but from what I can make of your requirement, this way you could outer join in your other table (on date) and change the case part (the 'then dt' should become 'then count(*)').

    If this is not clear or not giving you clues, then please elaborate. Add some testdata as well (create table + insert into statements)

  • 2. Re: Crosstab
    perforsgren Newbie
    Currently Being Moderated

    Hi.

    Tried to implement the proposed solution, but.... I found out that I had to deal with 168 different separate COUNT(*) , which of course take some time to execute, but maybe I did misunderstand your suggestion. Now looking for other alternatives.

    Please note that MON, TUE... as a predefined static column should contain (dynamically) today, yesterday, the day before that, and so on back to seven days ago. 

     

    PIVOT cannot be used since the columns are dynamically created at runtime. PIVOT cannot accept that. 

  • 3. Re: Crosstab
    Greg.Spall Expert
    Currently Being Moderated

    tweaking Hoek's query .. does this help any more?

     

    with hours as ( select to_char(trunc(sysdate)+level/24, 'hh24') hr  
                    from   dual  
                    connect by level <= 24  
                  )  
    ,    weekdays as ( select level-1 dy
                       from   dual  
                       connect by level <= 7  
                     )  
    select hr  
    ,      max(case when dy = 0 then trunc(sysdate)-dy end) today  
    ,      max(case when dy = 1 then trunc(sysdate)-dy end) today_1
    ,      max(case when dy = 2 then trunc(sysdate)-dy end) today_2
    ,      max(case when dy = 3 then trunc(sysdate)-dy end) today_3
    ,      max(case when dy = 4 then trunc(sysdate)-dy end) today_4
    ,      max(case when dy = 5 then trunc(sysdate)-dy end) today_5
    ,      max(case when dy = 6 then trunc(sysdate)-dy end) today_6
    from   hours  
    ,      weekdays  
    group by hr  
    order by hr;  

     

    Spits out:

     

    HR TODAY     TODAY_1   TODAY_2   TODAY_3   TODAY_4   TODAY_5   TODAY_6  
    -- --------- --------- --------- --------- --------- --------- ---------
    00 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    01 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    02 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    03 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    04 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    05 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    06 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    07 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    08 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    09 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    10 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    11 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    12 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    13 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    14 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    15 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    16 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    17 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    18 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    19 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    20 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    21 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    22 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    23 19-JUN-13 18-JUN-13 17-JUN-13 16-JUN-13 15-JUN-13 14-JUN-13 13-JUN-13
    24 rows selected.


    ??

  • 4. Re: Crosstab
    Peter vd Zwan Expert
    Currently Being Moderated

    Hi,

     

    Try like this:

     

    WITH A AS
    (
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') DT FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 02:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 03:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 04:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 05:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 06:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 07:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 02:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 03:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 04:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 05:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 06:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 07:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 02:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 03:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 04:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 05:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 06:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 07:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-18 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 07:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-12 08:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-13 23:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-14 02:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-17 03:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-17 04:00', 'YYYY-MM-DD HH24:MI') FROM DUAL UNION ALL
    SELECT TO_DATE('2013-06-17 05:00', 'YYYY-MM-DD HH24:MI') FROM DUAL
    )
    , B AS
    (
    SELECT TRUNC(DT) - TRUNC(SYSDATE) DY, TO_NUMBER(TO_CHAR(DT,'HH24')) HR FROM A
    )
    , C AS
    (
    SELECT LEVEL -1 HR FROM DUAL CONNECT BY LEVEL <= 24
    )
    , D AS
    (
    SELECT
      TO_CHAR(C.HR, 'FM00') HR
      ,B.DY
    FROM
      C
      LEFT JOIN B ON (C.HR = B.HR)
    )
    SELECT
      *
    FROM
      D
    PIVOT (COUNT(DY) FOR DY IN ( 0 AS "TODAY", -1 AS "DAY-1", -2 AS "DAY-2", -3 AS "DAY-3", -4 AS "DAY-4", -5 AS "DAY-5", -6 AS "DAY-6")
    )
    ORDER BY
      1
    HR  TODAY DAY-1 DAY-2 DAY-3 DAY-4 DAY-5 DAY-6
    --- ----- ----- ----- ----- ----- ----- -----
    00      0     0     0     0     0     0     0 
    01      6     3     0     0     0     0     0 
    02      2     1     0     0     0     1     0 
    03      2     1     1     0     0     0     0 
    04      2     1     1     0     0     0     0 
    05      2     1     1     0     0     0     0 
    06      2     1     0     0     0     0     0 
    07      3     1     0     0     0     0     0 
    08      3     2     0     0     0     0     0 
    09      0     0     0     0     0     0     0 
    10      0     0     0     0     0     0     0 
    11      0     0     0     0     0     0     0 
    12      0     0     0     0     0     0     0 
    13      0     0     0     0     0     0     0 
    14      0     0     0     0     0     0     0 
    15      0     0     0     0     0     0     0 
    16      0     0     0     0     0     0     0 
    17      0     0     0     0     0     0     0 
    18      0     0     0     0     0     0     0 
    19      0     0     0     0     0     0     0 
    20      0     0     0     0     0     0     0 
    21      0     0     0     0     0     0     0 
    22      0     0     0     0     0     0     0 
    23      0     0     0     0     0     0     1 
     24 rows selected 

     

     

    Regards,

     

    Peter

  • 5. Re: Crosstab
    Hoek Guru
    Currently Being Moderated

    Here's a rather quik dynamic solution I tend to use a lot lately:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238

     

    Please post a narrowed down testcase (creates, inserts or some other inventive, but at least sufficient, way to illustrate the problem) for us, if you can't translate the techniques used to your requirement.

    You'll have to understand about:

    Ref Cursors

    Dynamic SQL

    Pivot/Unpivot techniques (so, also the ones before PIVOT/UNPIVOT operators)

     

    Good starting points to always explore are (still):

    - this forum and it's dedicated, but (still) not easy to stumble upon SQL and PL/SQL FAQ @ https://forums.oracle.com/thread/2176552

    http://tkyte.blogspot.nl/2012/08/the-keys-to-oracle.html

    http://www.oracle-base.com/index.php

    http://www.oracle-developer.net/

  • 6. Re: Crosstab
    BluShadow Guru Moderator
    Currently Being Moderated

    Hoek wrote:

    - this forum and it's dedicated, but (still) not easy to stumble upon SQL and PL/SQL FAQ @ https://forums.oracle.com/thread/2176552

     

    Tell me about it...  I wish there was some way I could get it to stand out, but I'm tied to the limitations of the forum.

  • 7. Re: Crosstab
    perforsgren Newbie
    Currently Being Moderated

    This is so beautiful.

    I will adopt this framework according to my needs.

    Btw about test data: I had everything prepared in a zip file, but i find nowhere to save it (upload it) for you guys.

    Many thanks to all of you.

    However, this solution stands out !

     

    /Per

  • 8. Re: Crosstab
    perforsgren Newbie
    Currently Being Moderated

    Hi Peter

     

    Follow up question:

    SELECT TO_DATE('2013-06-19 01:00', 'YYYY-MM-DD HH24:MI') DT FROM DUAL UNION ALL

     

    Since I need to recreate the 'DT' another way, I need to know what the definition of it really stand for.

    In your example it is the first date occurrence on the last day within the requested time frame. Can you please give me some clarification regarding this?

     

    Best regards

    /Per

  • 9. Re: Crosstab
    Hoek Guru
    Currently Being Moderated

    DT is just a column alias in Peters' example. The first WITH (A) represents your actual table.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points