4 Replies Latest reply: Apr 7, 2012 5:37 AM by user11972299 RSS

    rows into columns display

    user11972299
      hi all,
      please help me with the script to achieve the following
      instance_n             Log_Date        Records_count
      ----------------------------------------------------------------------------------
      test          APR-06-2012               2814
      test          APR-05-2012               265
      test          APR-04-2012               127
      test          APR-03-2012               107440
      test          APR-02-2012               107362
      test          APR-01-2012               107330
      
      
      
      output required as
      
      Instance     APR-06     APR-05     APR-04     APR-03           APR-02
      ----------------------------------------------------------------------------------------------------------
      test          2814     265     127     107440          107362
      test2          2813     200     100     207330          207330
       
      table "Log_table" structure is
      intance_name varchar2
      log_date      date
      records_count     number



      I want to dislay the records for 5 previous days


      Thanks in advance for your help

      Edited by: user11972299 on Apr 6, 2012 9:30 AM

      Edited by: user11972299 on Apr 6, 2012 9:34 AM
        • 1. Re: rows into columns display
          Frank Kulash
          Hi,

          Here's one way:
          WITH     got_d          AS
          (
               SELECT     instance_n
               ,     CASE
                        WHEN  log_date >= TRUNC (SYSDATE)        THEN  0
                        WHEN  log_date >= TRUNC (SYSDATE) - 1  THEN  1
                        WHEN  log_date >= TRUNC (SYSDATE) - 2  THEN  2
                        WHEN  log_date >= TRUNC (SYSDATE) - 3  THEN  3
                                                    ELSE  4
                    END     AS d
               FROM     log_table
               WHERE     log_date     >= TRUNC (SYSDATE) - 4
               AND     log_date     <  TRUNC (SYSDATE) + 1
          )
          SELECT       instance_n
          ,       COUNT (CASE WHEN d = 0 THEN 1 END)     AS today
          ,       COUNT (CASE WHEN d = 1 THEN 1 END)     AS today_minus_1
          ,       COUNT (CASE WHEN d = 2 THEN 1 END)     AS today_minus_2
          ,       COUNT (CASE WHEN d = 3 THEN 1 END)     AS today_minus_3
          ,       COUNT (CASE WHEN d = 4 THEN 1 END)     AS today_minus_4
          FROM       got_d
          GROUP BY  instance_n;
          ;
          This will work in Oracle 9 or higher. If you have Oracle 11, you could also use SELECT ... PIVOT.

          To get dynamic column names (such as "APR-06", instead of the generic "TODAY") requires dynamic SQL. In SQL*Plus, that isn't very hard.
          • 2. Re: rows into columns display
            user11972299
            Thanks for reply
            I am getting following output when i run your query
            test     1     1     1     0     0
            test1     1     1     1     1     1
            test3     1     1     1     1     1
            • 3. Re: rows into columns display
              Frank Kulash
              Hi,
              user11972299 wrote:
              Thanks for reply
              I am getting following output when i run your query ...
              Is that good or bad?

              if that's not what you want, then post your sample data (CREATE TABLE and INSERT statements) and the correct results you want from that sample data.
              Always say which version of Oracle you're using.
              • 4. Re: rows into columns display
                user11972299
                Frank,
                unfortunately it is bad.
                I need output as mentioned in my first post. I am on 9i, 10G and 11G.
                if it is version dependent, i will move the report query to the version which provides desired solution


                regards
                Mir