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:
select * from table( pivot( 'select * from city_connections' ) )
which has the following data:
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:
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:
GROUP BY order_source, ROLLUP (status) order by case when status = 'GRAND TOTAL' THEN 1 ELSE 2 END;
Struggling to get the grand total in the bottom row. But now I need the grand total column.
Current progress is as below: 
My View is created as follows, simplified, which is giving me the current progress image:
CREATE OR REPLACE VIEW TEST1 AS SELECT NVL(status, 'GRAND TOTAL') AS row_labels ,order_source ,count(1) Count# FROM SOMETABLE GROUP BY order_source, ROLLUP (status) order by case when status = 'GRAND TOTAL' THEN 2 ELSE 1 END; SELECT * FROM TABLE( pivot('SELECT * FROM TEST1') );
Working towards to create the query which will also select that missing column.
Message was edited by: MelB-Oracle
External link redacted.