How to get this o/p from the sql query

[krishnaindia2007]

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;

   INVOICE TOT_AMOUNT    01/2008    01/2008    01/2008    01/2008    01/2008
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       5000        100        200
         2       5000                              400        500
         3       5000                                                    600

[bhaski]

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

[peeyush_jain]

Thanks for soultion , I agree with you.