1 2 Previous Next 21 Replies Latest reply: Oct 10, 2012 10:15 AM by BluShadow RSS

    Please help with report format

    Chandran
      Hi,

      When i am queryiing my table the result is like

      select * from general;
      MONTH    REGISTERD   ACCEPTED   REJECTED 
      2012-04        120             80               40 
      2012-05          80             40               40 
      2012-05         60             40               20 
      2012-04         90             40               50 
      
      How i can display my result like below :
      
                       2012-04    2012-05
      registered       210          170
      accepted        120           80
      rejected           90           60
      Thanks
      Chandran.

      Edited by: Chandran on Oct 3, 2012 9:02 AM
        • 1. Re: Please help with report format
          sb92075
          Chandran wrote:
          Hi,

          When i am queryiing my table the result is like

          select * from general;
          MONTH    REGISTERD   ACCEPTED   REJECTED 
          2012-04        120             80               40 
          2012-05          80             40               40 
          2012-05         60             40               20 
          2012-04         90             40               50 
          
          How i can display my result like below :
          
          2012-04    2012-05
          registered       210          170
          accepted        120           80
          rejected           90           60
          Thanks
          Chandran.

          Edited by: Chandran on Oct 3, 2012 9:02 AM
          PIVOT by Frank Help for a query to add columns
          PIVOT by TomK http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740
          • 2. Re: Please help with report format
            Purvesh K
            You need to Unpivot:
            with data as
            (
              select '2012-04' mon, 120 reg, 80 acc, 40 rej from dual union all
              select '2012-05' mon, 80 reg, 40 acc, 40 rej from dual union all
              select '2012-05' mon, 60 reg, 40 acc, 20 rej from dual union all
              select '2012-04' mon, 90 reg, 40 acc, 50 rej from dual
            )
            select status, sum(quantity) sm_regs
              from (
                    select *
                      from data
                     unpivot (quantity for status in (reg as 'Registered', acc as 'Accepted', rej as 'Rejected'))
                   )
             group by status;
            
            STATUS     SM_REGS
            ---------- -------
            Accepted       200 
            Registered     350 
            Rejected       150 
            • 3. Re: Please help with report format
              Chandran
              Thank you somuch Pruvesh,

              Your solution works, but i want to display the result in month wise not total sum.

              Please help me on this, as i am little new to PIVOT concept..
              instead of  :
              sm_regs
              200
              350
              150
              
              like below:
              2012-04  2012-05
                120        80
                210       170
                  90         60  
               
              Thanks
              Chandran
              • 4. Re: Please help with report format
                Frank Kulash
                Hi, Chandran,

                >
                ... but i want to display the result in month wise not total sum.

                Please help me on this, as i am little new to PIVOT concept.. "Pivot" means you have data in 1 column on multiple rows, and you want to display it as multiple columns on 1 row.
                "Unpivot" means you have data in multiple columns on 1 row, and you want to display it as 1 column on multiple rows.
                Purvesh showed you how to unpivot, but in this problem, you want to do both pivot and unpivot. What were originally rows, you want to display as columns. What were originally columns, you want to display as rows.
                See {message:id=3582882} and {message:id=4090848} for examples of how to do that.
                • 5. Re: Please help with report format
                  Chandran
                  Hi Frank thanks for your reply,

                  I will go through you links, in my case

                  CREATE TABLE  GENERAL
                     (     ID NUMBER, 
                       TYPE VARCHAR2(20), 
                       MONTH VARCHAR2(20), 
                       REGISTERED NUMBER, 
                       ACCEPTED NUMBER, 
                       REJECTED NUMBER
                     )
                  
                  insert into general values (1,'A','2012-04',50,40,10);
                  
                  insert into general values (2,'B','2012-05',60,40,20);
                  
                  insert into general values (3,'A','2012-04',70,40,30);
                  
                  insert into general values (4,'C','2012-05',80,40,40);
                  
                  insert into general values (5,'B','2012-04',90,40,50);
                  
                  when i am querying table : select * from general;
                  
                  ID     TYPE       MONTH     REGISTERED     ACCEPTED        REJECTED
                  1       A            2012-04         50                          40           10
                  2       B            2012-05         60                          40           20
                  3       A            2012-04         70                          40           30 
                  4       C            2012-05         80                      40           40
                  4       B            2012-04         90                      40           50
                  
                  select
                  month, 
                  sum(registered) Registered,
                  sum(accepted)Accepted,
                  sum(rejected)rejected from general group by type,month;
                  
                  result : 
                  MONTH      REGISTERED             ACCEPTED               REJECTED
                  2012-05             80                            40                           40
                  2012-04            120                            80                           40
                  2012-04             90                            40                           50
                  2012-05             60                            40                           20
                  
                  required result : 
                  
                                       2012-04             2012-05
                  REGISTERED       210                   140
                  ACCEPTED          120                    80
                  REJECTED            90                     60
                  Please help me on this...

                  Thanks
                  Chandran

                  Edited by: Chandran on Oct 4, 2012 6:14 AM
                  • 6. Re: Please help with report format
                    Frank Kulash
                    Hi,
                    Chandran wrote:
                    Hi Frank thanks for your reply,

                    I will go through you links, ...
                    Perfect! If you have trouble adapting those solutions to your problem, then post your best attempt, and a specific question.
                    Which version of Oracle you're using is always important, but it is even more important than usual in this case. The SELECT UNPIVOT and PIVOT features that were new in Oracle 11.1 make this much simpler. Always say which version of Oracle you're using.
                    select
                    month, 
                    sum(registered) Registered,
                    sum(accepted)Accepted,
                    sum(rejected)rejected from general group by type,month;
                    
                    result : 
                    MONTH      REGISTERED             ACCEPTED               REJECTED
                    2012-05             80                            40                           40
                    2012-04            120                            80                           40
                    2012-04             90                            40                           50
                    2012-05             60                            40                           20
                    
                    required result : 
                    
                    2012-04             2012-05
                    REGISTERED       210                   140
                    ACCEPTED          120                    80
                    REJECTED            90                     60
                    If you don't want separate totals for type='A' and type='B', then don't include type in the GROUP BY clause.
                    • 7. Re: Please help with report format
                      Chandran
                      Hi,

                      I am using 11.1 version

                      as you said i dont want use type and query is like :
                      select
                      month, 
                      sum(registered) Registered,
                      sum(accepted)Accepted,
                      sum(rejected)rejected from general group by month;
                      
                      and required result like:
                      
                      
                       
                                           2012-04             2012-05
                      REGISTERED       210                   140
                      ACCEPTED          120                    80
                      REJECTED            90                     60
                      Thanks
                      Chandran

                      Edited by: Chandran on Oct 4, 2012 7:08 AM

                      Edited by: Chandran on Oct 4, 2012 7:09 AM
                      • 8. Re: Please help with report format
                        Frank Kulash
                        Hi,
                        Chandran wrote:
                        Hi,

                        I am using 11.1 version
                        Good! Then the 2nd link I posted, that is {message:id=4093163} , should be very close to what you want.
                        as you said i dont want use type and query is like : ...
                        That must be the query you had before reading the link above.
                        If you have trouble adapting that solution, then post your code, and a specific question.
                        • 9. Re: Please help with report format
                          Chandran
                          Hi,

                          Thanks for your reply Frank, I have tried your solution but but can't seem to find a way to make this work.


                          My base table data like :
                          select * from general;
                          
                          ID     TYPE              MONTH               REGISTERED      ACCEPTED         REJECTED
                          1       A            2012-04                 50                          40           10
                          2       B            2012-05                 60                          40           20
                          3       A            2012-04                 70                          40           30 
                          4       C            2012-05                 80                      40           40
                          4       B            2012-04                 90                      40           50
                          when i am applying the below query :
                          select
                          month, 
                          sum(registered) Registered,
                          sum(accepted)Accepted,
                          sum(rejected)rejected from general group by month;
                          
                          MONTH     REGISTERED     ACCEPTED       REJECTED
                          2012-05        140                         80         60
                          2012-04        210                        120         90
                          i should get the below format :
                                                    2012-04                   2012-05
                          REGISTERED           210                         140
                          ACCEPTED             120                           80
                          REJECTED               90                           60
                          below are SQL commands :
                          CREATE TABLE  GENERAL
                             (     ID NUMBER, 
                               TYPE VARCHAR2(20), 
                               MONTH VARCHAR2(20), 
                               REGISTERED NUMBER, 
                               ACCEPTED NUMBER, 
                               REJECTED NUMBER
                             );
                          
                          insert into general values (1,'A','2012-04',50,40,10);
                          
                          insert into general values (2,'B','2012-05',60,40,20);
                          
                          insert into general values (3,'A','2012-04',70,40,30);
                          
                          insert into general values (4,'C','2012-05',80,40,40);
                          
                          insert into general values (5,'B','2012-04',90,40,50);
                          Thanks
                          Chandran

                          Edited by: Chandran on Oct 4, 2012 9:55 AM

                          Edited by: Chandran on Oct 4, 2012 9:56 AM
                          • 10. Re: Please help with report format
                            Frank Kulash
                            Hi,

                            You keep posting queries as if you haven't read {message:id=4093163} .
                            Read it, and adapt that solution to your table. If you're trying, but you get stuck, then people (myself included) will be glad to help you. If you're not trying, that's a different story. If you can't get the solution from that link to work for your table, then post what you tried.
                            • 11. Re: Please help with report format
                              Chandran
                              Thanks Frank,

                              I will follow the above.

                              regards
                              Chandran
                              • 12. Re: Please help with report format
                                Chandran
                                Hi Frank,

                                I am just trying to UNPIVOT the month filed and PIVOT the type fields using below code
                                SELECT
                                id,
                                type,
                                MONTH1,
                                MONTH2
                                
                                FROM GENERAL
                                UNPIVOT(AMT FOR TYPE IN ( 'registered' AS REGISTERED,'accepted' AS ACCEPTED,'rejected' AS REJECTED))
                                PIVOT(SUM(AMT) FOR MONTH IN ('2012-04' AS MONTH1,'2012-05' AS MONTH2))
                                getting ORA-00904: : invalid identifier error, unable to find out the right way. Please help me on this.

                                Thanks
                                Chandran
                                • 13. Re: Please help with report format
                                  Frank Kulash
                                  Hi, Chandran,
                                  Chandran wrote:
                                  Hi Frank,

                                  I am just trying to UNPIVOT the month filed and PIVOT the type fields using below code
                                  SELECT
                                  id,
                                  type,
                                  MONTH1,
                                  MONTH2
                                  
                                  FROM GENERAL
                                  UNPIVOT(AMT FOR TYPE IN ( 'registered' AS REGISTERED,'accepted' AS ACCEPTED,'rejected' AS REJECTED))
                                  PIVOT(SUM(AMT) FOR MONTH IN ('2012-04' AS MONTH1,'2012-05' AS MONTH2))
                                  Toggle the quotes in the UNPIVOT clause.
                                  The syntax of UNPIVOT is:
                                  UNPIVOT (    new_col) 
                                       FOR  label_col  IN ( old_co1_1  AS label_1_val
                                                         , old_col_2     AS label_2_val
                                                    , ...
                                                    )
                                       )
                                  UNPIVOT takes n columns on 1 row, and produces 2 columns on n rows.
                                  One of those new output columns (new_col) is the value taken from the old_cols. The example above takes the existing columns old_col_1, col_col_2, ... and puts their values into new_col.
                                  How can you tell which row of output came from which old_col? That's what the other output column is for: label_col will contain a value that you give to identify where the value came from.
                                  In the example above, sll the expressions ending in col are column names.  You'll want to give literals for all the the expressions ending in val, like this:
                                  UNPIVOT       (   amt  FOR action_type IN ( registered     AS 'REGISTERED'
                                                                         , accepted     AS 'ACCEPTED'
                                                            ,     rejected     AS 'REJECTED'
                                                            )
                                         )
                                  with single-quotes after the AS keywords, not before them.

                                  Also, PIVOT assumes you want to GROUP BY all the columns in your table that are not named in the PIVOT clause. That includes columns that are not used in the query, so very often, when using PIVOT, you have to do something like this
                                  WITH  relevant_columns  AS
                                  (
                                       SELECT col1, col2, col3, ...
                                       FROM   actual_table
                                       WHERE  ...
                                  )
                                  SELECT     col1, ...
                                  FROM     relevant_columns     -- NOT actual_table
                                  ...
                                  PIVOT     ...
                                  ;
                                  • 14. Re: Please help with report format
                                    jeneesh
                                    SQL> SELECT status,
                                      2         sum(month1_amt) month1_amt,
                                      3         sum(month2_amt) month2_amt
                                      4  FROM GENERAL
                                      5  UNPIVOT(AMT FOR status IN ( registered AS 'REGISTERED',accepted AS 'ACCEPTED',rejected AS 'REJECTED'))
                                      6  PIVOT(SUM(AMT) as amt FOR MONTH IN ('2012-04' AS MONTH1,'2012-05' AS MONTH2))
                                      7  group by status;
                                    
                                    STATUS     MONTH1_AMT MONTH2_AMT
                                    ---------- ---------- ----------
                                    REGISTERED        210        140
                                    REJECTED           90         60
                                    ACCEPTED          120         80
                                    Edited by: jeneesh on Oct 5, 2012 6:20 PM
                                    Forgot to refresh, did not see Frank's reply..
                                    1 2 Previous Next