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

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;

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

Thanks for soultion , I agree with you.

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