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:
select * from (
select (service, area, result from sample\_table
)
pivot xml (
max(result)
for area
in (select area from sample\_table)
)
order by "service"
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.
UPDATE
When I was looking from older discussions, @"fac586" gave this accepted answer , 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!