Answering my own question:
In the end I gave up using manual sql PIVOT and used the PIVOT feature provided from the Interactive Report's Action > Format > Pivot.
1 person found this helpful
Hi guys, to illustrate my problem, I will use this screenshot:
Currently I have the table on the right, and I want to create a report just like the table on the left. The first solution that came into my mind was using PIVOT, for example:
But Oracle SQL does not support dynamic number of columns (for the IN keyword), and using XML keyword will return [unsupported data type] message on the report. Is there another approach to solve this problem?
P.S. I am using Apex v5.0.4 and DB v12.
When I was looking from older discussions, fac586 gave this accepted answer Re: Pivot query using XML option in APEX , mentioning serializing the XML and then shredding the XMLType in the report. This seems close to what I am looking for, but can someone please give more detailed step-by-step on how to shred the XMLType into the report? Thank you!
I created a demonstration of that in response to another thread: Matrix report
Another option is to use a PL/SQL Function Body returning SQL Query report source with a dynamic pivot specification: PL/SQL RETURNING SQL QUERY
However, neither of those options is possible in an interactive report due to the dynamic number of columns involved (among other things), so if an interactive report is a requirement you'll have to stick to the built-in IR pivot feature.