How to transfer left join statement into pivot?

2611484 · May 12, 2014 10:46PM

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;

Krishna-Oracle · May 13, 2014 12:23AM

Have a look at

http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

Forum Stats

Quick Links

Categories

How to transfer left join statement into pivot?

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How to transfer left join statement into pivot?

Answers