Results 1 to 1 of 1
  1. #1
    Join Date
    May 2014
    Posts
    2

    Unanswered: How to transfer into pivot?

    Please help me, I have a long script using left join statement 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. Please help me with this. 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;
    Last edited by tenkepadu; 05-13-14 at 00:48.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •