Forum Stats

  • 3,853,207 Users
  • 2,264,192 Discussions
  • 7,905,286 Comments

Discussions

Oracle SQL - Dynamic Pivot with grand total

user13370731
user13370731 Member Posts: 2
edited Jul 18, 2014 4:30PM in SQL & PL/SQL

I have a requirement to create a query which had dynamic column data and this had to be pivoted as rows. I found a really nice function which does this and the source code is at the bottom:

The additional step that I need is to get the grand totals of the rows and columns. If I take the last example from the above article:

<span class="kwd" style="color: #00008b; background: transparent;">select</span><span class="pln" style="background: transparent;"> </span><span class="pun" style="background: transparent;">*</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">from</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">table</span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">pivot</span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;">  </span><span class="str" style="color: #800000; background: transparent;">'select * from city_connections'</span><span class="pln" style="background: transparent;"> </span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> </span><span class="pun" style="background: transparent;">)</span>

which has the following data:

enter image description here

He created a view of his query, I did the same. But basically now I would like to have another row and column be added which will give me the total. For example some thing like below:

enter image description here

If I do a ROLLUP('Row Labels') in my original query and then run the pivot function, then I am getting a last null row. Any suggestions would be great, thank you.

I have managed to get the GRAND TOTAL of the rows as:

<span class="kwd" style="color: #00008b; background: transparent;">GROUP</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">BY</span><span class="pln" style="background: transparent;"> order_source</span><span class="pun" style="background: transparent;">,</span><span class="pln" style="background: transparent;"> ROLLUP </span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;">status</span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">order</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">by</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">case</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">when</span><span class="pln" style="background: transparent;"> status </span><span class="pun" style="background: transparent;">=</span><span class="pln" style="background: transparent;"> </span><span class="str" style="color: #800000; background: transparent;">'GRAND TOTAL'</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">THEN</span><span class="pln" style="background: transparent;"> </span><span class="lit" style="color: #800000; background: transparent;">1</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">ELSE</span><span class="pln" style="background: transparent;"> </span><span class="lit" style="color: #800000; background: transparent;">2</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">END</span><span class="pun" style="background: transparent;">;</span>

Struggling to get the grand total in the bottom row. But now I need the grand total column.

Current progress is as below: enter image description here

My View is created as follows, simplified, which is giving me the current progress image:

<span class="kwd" style="color: #00008b; background: transparent;">CREATE</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">OR</span><span class="pln" style="background: transparent;"> REPLACE </span><span class="kwd" style="color: #00008b; background: transparent;">VIEW</span><span class="pln" style="background: transparent;"> TEST1 </span><span class="kwd" style="color: #00008b; background: transparent;">AS</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">SELECT</span><span class="pln" style="background: transparent;"> NVL</span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;">status</span><span class="pun" style="background: transparent;">,</span><span class="pln" style="background: transparent;"> </span><span class="str" style="color: #800000; background: transparent;">'GRAND TOTAL'</span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">AS</span><span class="pln" style="background: transparent;"> row_labels </span><span class="pun" style="background: transparent;">,</span><span class="pln" style="background: transparent;">order_source </span><span class="pun" style="background: transparent;">,</span><span class="pln" style="background: transparent;">count</span><span class="pun" style="background: transparent;">(</span><span class="lit" style="color: #800000; background: transparent;">1</span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> Count</span><span class="pun" style="background: transparent;">#</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">FROM</span><span class="pln" style="background: transparent;"> SOMETABLE </span><span class="kwd" style="color: #00008b; background: transparent;">GROUP</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">BY</span><span class="pln" style="background: transparent;"> order_source</span><span class="pun" style="background: transparent;">,</span><span class="pln" style="background: transparent;"> ROLLUP </span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;">status</span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">order</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">by</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">case</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">when</span><span class="pln" style="background: transparent;"> status </span><span class="pun" style="background: transparent;">=</span><span class="pln" style="background: transparent;"> </span><span class="str" style="color: #800000; background: transparent;">'GRAND TOTAL'</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">THEN</span><span class="pln" style="background: transparent;"> </span><span class="lit" style="color: #800000; background: transparent;">2</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">ELSE</span><span class="pln" style="background: transparent;"> </span><span class="lit" style="color: #800000; background: transparent;">1</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">END</span><span class="pun" style="background: transparent;">;</span><span class="pln" style="background: transparent;">  </span><span class="kwd" style="color: #00008b; background: transparent;">SELECT</span><span class="pln" style="background: transparent;"> </span><span class="pun" style="background: transparent;">*</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">FROM</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">TABLE</span><span class="pun" style="background: transparent;">(</span><span class="pln" style="background: transparent;"> </span><span class="kwd" style="color: #00008b; background: transparent;">pivot</span><span class="pun" style="background: transparent;">(</span><span class="str" style="color: #800000; background: transparent;">'SELECT * FROM TEST1'</span><span class="pun" style="background: transparent;">)</span><span class="pln" style="background: transparent;"> </span><span class="pun" style="background: transparent;">);</span>

Working towards to create the query which will also select that missing column.

Message was edited by: MelB-Oracle External link redacted.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Jul 18, 2014 4:40PM

    Hi

    user13370731 wrote:
    
    I have a requirement to create a query which had dynamic column data and this had to be pivoted as rows. I found a really nice function which does this and the source code is at the bottom:
    The additional step that I need is to get the grand totals of the rows and columns. If I take the last example from the above article:
     

    I don't see any article above.

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

    Include any user-defined functions you need.  If they are available on some other web site, then all you have to do is copy and paste them from that web site.

    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

  • user13370731
    user13370731 Member Posts: 2
    edited Jul 18, 2014 4:30PM

    Message was edited by: MelB-Oracle External link redacted.

    The mods removed the link. Its on stack as well, same title.

This discussion has been closed.