Please help me, I have a long script and I want it to be dynamically and flexible and I want to use pivot, so that I will not modified the script if ever there's a new item need to add, but I'm not familiar in pivot query in oracle. Thank you.
Here's the script:
select 'Data'
||','||to_char(d.dtime_day,'MM/dd/yyyy')
||','||nvl(ss1.total,0)
||','||nvl(ss2.total,0)
||','||nvl(ss3.total,0)
from table1 d
left join(
select trunc(call_date, 'IW') as ddate
,count(dst_channel) as total
from table2
where dst_channel like 'item1%'
and status like 'ANSWERED%'
group by trunc(call_date, 'IW')
) ss1 on d.dtime_day = ss1.ddate
left join(
select trunc(call_date, 'IW') as ddate
,count(dst_channel) as total
from table2
where dst_channel like 'item2%'
and status like 'ANSWERED%'
group by trunc(call_date, 'IW')
) ss2 on d.dtime_day = ss2.ddate
left join(
select trunc(call_date, 'IW') as ddate
,count(dst_channel) as total
from table2
where dst_channel like 'item3%'
and status like 'ANSWERED%'
group by trunc(call_date, 'IW')
) ss3 on d.dtime_day = ss3.ddate
where d.dtime_day between trunc(sysdate,'IW')-12*7 and trunc(sysdate) -1
and trunc(d.dtime_day, 'IW')= d.dtime_day
order by d.dtime_day;