Forum Stats

  • 3,851,567 Users
  • 2,264,000 Discussions
  • 7,904,781 Comments

Discussions

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

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Oct 12, 2016 2:48AM in APEX Discussions

Hi guys, to illustrate my problem, I will use this screenshot:

Screen Shot 2016-10-07 at 1.06.40 PM.png

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!

Tagged:
DannyS-Oracle

Best Answer

  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Oct 11, 2016 8:45PM Answer ✓

    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.

Answers

  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Oct 11, 2016 8:45PM Answer ✓

    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
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Oct 12, 2016 2:48AM
    DannyS-Oracle wrote:Hi guys, to illustrate my problem, I will use this screenshot:
    Screen Shot 2016-10-07 at 1.06.40 PM.png

    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.

    DannyS-OracleDannyS-Oracle
This discussion has been closed.