https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547
Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
http://www.oracle-developer.net/display.php?id=506
Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238
Then I found this gem, courtesy of Anton Scheffer, via Lucas Jellema
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
The link to Anton's blog post no longer works, but the relevant sql zip is still available in the resources.
It accepts queries like this, which solves my current problem:
select * from table( pivot( 'select deptno, job, count(*) c from scott.emp group by deptno, job' ) )
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30 1 1 4
20 2 2 1
10 1 1 1
What an awesome solution.
For anyone receiving this error:
ORA-29913: error in executing ODCITABLEDESCRIBE callout
check you have direct SELECT privilege on table, similar to resolving PLS-201 error.
Now to work it into an APEX region, which seems like will be a little wild when applying PPR on report with generic number of columns.
Hey Scott,
ReplyDeleteYou may be interested to look at my answer to this question on StackOverflow:
http://stackoverflow.com/questions/23262254/is-the-following-query-possible-with-sql-pivot/
Someone asked this pivot question there aswell, and I provided an answer with pivot and xml in it. Anton's code is great of course, I'm only providing an alternative, and you've explored on that road too. This is for classic reports though.
I didn't touch on tabular forms in it, since that was not part of the question. But I've done it plenty of times so far and should take some time to work on an example. Main use-case: financial data, pivot by year or something else. But it certainly works great.
Hey Tom,
ReplyDeleteThat was a pretty comprehensive answer on stackoverflow. I'm having difficulty comprehending and adapting the XML component, and since your solution uses dynamic region it's no good for PPR.
I'm not convinced Paul's OTN solution provides any advantage over what I already have with the function in a classic report. Except maybe using bind variables better, though it seems I'd need to maintain separate query for column headings. And comprehend xml!
Thanks for the link though, that was interesting. Seems I even commented on Paul's matrix solution a while ago.
As commented in the related OTN post I gave the XML version a go. It behaves as I'd expect, seemingly faster, and I think will be just as flexible.
ReplyDeleteI think I'll do a follow up showing my experiences deploying to APEX
Hey Scott,
ReplyDeleteI'm catching up some blog posts after my hollidays, that's why I react a bit late ;-)
A few weeks before you posted this I need the same functionality(a dynamic pivot). I was planning to write a blog post about it, but didn't find the time to do it yet.
I had to create a CSV file with a pivot table for products in a product group. Different product groups have different numbers of products, so it had to be a dynamic pivot.
So I used pivot XML in PL/SQL.
Short: I created a cursor for the pivot, I used XMLTABLE to "breakdown" the XML output so I could easily write it to a file and calculate some totals.
I'll try to put this in a blog post one of the next days.
This is still a work in progress, it has been suspended with some APEX 5 upgrade work ;p
ReplyDeleteI'd say my final solution will be a simpler XML query, but I'm having difficulty because my cells are markup text, not numerics.