Forum Stats

  • 3,851,919 Users
  • 2,264,053 Discussions
  • 7,904,904 Comments

Discussions

How to transfer left join statement into pivot?

2611484
2611484 Member Posts: 29
edited May 13, 2014 12:23AM in SQL & PL/SQL

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:

<span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">select</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'Data'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">','</span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="pln" style="font-size: 13.333333969116211px;">to_char</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'MM/dd/yyyy'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">','</span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="pln" style="font-size: 13.333333969116211px;">nvl</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">ss1</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">total</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">0</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">','</span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="pln" style="font-size: 13.333333969116211px;">nvl</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">ss2</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">total</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">0</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">','</span><span class="pun" style="font-size: 13.333333969116211px;">||</span><span class="pln" style="font-size: 13.333333969116211px;">nvl</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">ss3</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">total</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">0</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">from</span><span class="pln" style="font-size: 13.333333969116211px;"> table1 d<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">left</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">join</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">select</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> ddate<br/>   </span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;">count</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">dst_channel</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> total<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">from</span><span class="pln" style="font-size: 13.333333969116211px;"> table2<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">where</span><span class="pln" style="font-size: 13.333333969116211px;"> dst_channel </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'item1%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> status </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'ANSWERED%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">group</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">by</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/> </span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> ss1 </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">on</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day </span><span class="pun" style="font-size: 13.333333969116211px;">=</span><span class="pln" style="font-size: 13.333333969116211px;"> ss1</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">ddate<br/> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">left</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">join</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">select</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> ddate<br/>   </span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;">count</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">dst_channel</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> total<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">from</span><span class="pln" style="font-size: 13.333333969116211px;"> table2<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">where</span><span class="pln" style="font-size: 13.333333969116211px;"> dst_channel </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'item2%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> status </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'ANSWERED%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">group</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">by</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/> </span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> ss2 </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">on</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day </span><span class="pun" style="font-size: 13.333333969116211px;">=</span><span class="pln" style="font-size: 13.333333969116211px;"> ss2</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">ddate<br/> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">left</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">join</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">select</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> ddate<br/>   </span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;">count</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">dst_channel</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> total<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">from</span><span class="pln" style="font-size: 13.333333969116211px;"> table2<br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">where</span><span class="pln" style="font-size: 13.333333969116211px;"> dst_channel </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'item3%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> status </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'ANSWERED%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">group</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">by</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">call_date</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>  </span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> ss3 </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">on</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day </span><span class="pun" style="font-size: 13.333333969116211px;">=</span><span class="pln" style="font-size: 13.333333969116211px;"> ss3</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">ddate<br/> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">where</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">between</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">sysdate</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">-12</span><span class="pun" style="font-size: 13.333333969116211px;">*</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">7</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">sysdate</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">-1</span><span class="pln" style="font-size: 13.333333969116211px;"><br/> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)=</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day<br/> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">order</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">by</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day</span><span class="pun" style="font-size: 13.333333969116211px;">;</span>

Tagged:

Answers

This discussion has been closed.