Results 1 to 5 of 5

Thread: How to get this o/p from the sql query

  1. #1
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    How to get this o/p from the sql query

    Hi i have the data set like this -->


    with t as
    (
    select 1 as invoice,5000 as tot_amount,'1/1/2008' as pay_date,100 as payment from dual
    union
    select 1 ,5000 ,'5/1/2008' ,200 from dual
    union
    select 2 ,5000 ,'10/1/2008' ,400 from dual
    union
    select 2 ,5000 ,'15/1/2008' ,500 from dual
    union
    select 3 ,5000 ,'19/1/2008' ,600 from dual
    )
    select
    invoice,tot_amount,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),1,payment))day_1,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),5,payment))day_5,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),10,payment))day_10,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),15,payment))day_15,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),19,payment))day_19
    from t
    group by
    invoice,tot_amount
    order by 1

    it's o/p is


    INVOICE TOT_AMOUNT DAY_1 DAY_5 DAY_10 DAY_15 DAY_19
    -----------------------------------------------------------------------
    1 5000 100 200
    2 5000 400 500
    3 5000 600

    Now how i can replace the header like day_1 with it's corresponding month/year format?

    INVOICE TOT_AMOUNT 1/2008 5/2008 10/2008 15/2008 19/2008
    ------------------------------------------------------------------------
    1 5000 100 200
    2 5000 400 500
    3 5000 600

    Thanks in advance


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: How to get this o/p from the sql query

    set verify off

    col day_1 new_value day_1
    col day_5 new_value day_5
    col day_10 new_value day_10
    col day_15 new_value day_15
    col day_19 new_value day_19

    select to_Char(to_date('1/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_1 from dual;
    select to_Char(to_date('5/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_5 from dual;
    select to_Char(to_date('10/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_10 from dual;
    select to_Char(to_date('15/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_15 from dual;
    select to_Char(to_date('19/1/2008','DD/MM/YYYY'),'MM/YYYY') as day_19 from dual;

    with t as
    (
    select 1 as invoice,5000 as tot_amount,'1/1/2008' as pay_date,100 as payment from dual
    union
    select 1 ,5000 ,'5/1/2008' ,200 from dual
    union
    select 2 ,5000 ,'10/1/2008' ,400 from dual
    union
    select 2 ,5000 ,'15/1/2008' ,500 from dual
    union
    select 3 ,5000 ,'19/1/2008' ,600 from dual
    )
    select
    invoice,tot_amount,
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),1,payment)) "&day_1",
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),5,payment)) "&day_5",
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),10,payment)) "&day_10",
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),15,payment)) "&day_15",
    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),19,payment)) "&day_19"
    from t
    group by
    invoice,tot_amount
    order by 1;
    Code:
       INVOICE TOT_AMOUNT    01/2008    01/2008    01/2008    01/2008    01/2008
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1       5000        100        200
             2       5000                              400        500
             3       5000                                                    600



  3. #3
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: How to get this o/p from the sql query

    Thanx for reply.

    I found a very very good link regarding this solution.This much similar as yr but little modified.

    http://asktom.oracle.com/pls/asktom/...25000346503967


  4. #4
    Expert Member
    Join Date
    Dec 2007
    Answers
    138

    Re: How to get this o/p from the sql query

    Thanks for soultion , I agree with you.


  5. #5
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Re: How to get this o/p from the sql query

    max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),1,payment)) "&day_1",


  6.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact