9 Replies Latest reply on Jun 20, 2013 1:14 PM by Hoek

    Crosstab

    perforsgren

      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

          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

            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

              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.
              


              ??

              1 person found this helpful
              • 4. Re: Crosstab
                Peter vd Zwan

                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

                  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/

                  1 person found this helpful
                  • 6. Re: Crosstab
                    BluShadow

                    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

                      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

                        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

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