2 Replies Latest reply on Oct 12, 2016 6:48 AM by fac586

    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:

      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 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!

        • 1. Re: How to generate dynamic columns for a report while using PIVOT in the source query?
          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.

          • 2. Re: How to generate dynamic columns for a report while using PIVOT in the source query?
            fac586

            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"

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

            1 person found this helpful