How to generate dynamic columns for a report while using PIVOT in the source query?

DannyS-Oracle

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!

DannyS-Oracle

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.

I decided to give up on retaining the "Pass" or "Fail" values since none of the aggregate functions (MIN, MAX, COUNT) support varchar type. So I changed the values to numbers and manually color-coded the cells based on the numbers using JavaScript (e.g. Green for "Pass" and Red for "Fail"). Looks OK for me.

DannyS-Oracle

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.

I decided to give up on retaining the "Pass" or "Fail" values since none of the aggregate functions (MIN, MAX, COUNT) support varchar type. So I changed the values to numbers and manually color-coded the cells based on the numbers using JavaScript (e.g. Green for "Pass" and Red for "Fail"). Looks OK for me.

fac586

DannyS-Oracle wrote: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:

1. select*from(
2. select(service,area,resultfromsample_table
3. )
4. pivotxml(
5. max(result)
6. forarea
7. in(selectareafromsample_table)
8. )
9. orderby"service"

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.UPDATEWhen 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:

Another option is to use a PL/SQL Function Body returning SQL Query report source with a dynamic pivot specification:

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.