This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 10, 2012 8:15 AM by BluShadow RSS

Please help with report format

Chandran Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Frank,

    I will follow the above.

    regards
    Chandran
  • 12. Re: Please help with report format
    Chandran Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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