7 Replies Latest reply: Mar 4, 2014 8:53 AM by Stew Ashton RSS

    Dynamic pivoting with subquery

    Prashant Dabral

      Hi,

       

      WITH T (ID,FUND, VAL) AS

      (SELECT  1, 'W'   ,23 FROM DUAL

      UNION ALL

      SELECT  1, 'X'   ,24 FROM DUAL

      UNION ALL

      SELECT  1, 'Y'   ,25 FROM DUAL

      UNION ALL

      SELECT  2, 'W'   ,23 FROM DUAL

      UNION ALL

      SELECT  2, 'V'   ,22 FROM DUAL)

      SELECT *

      FROM T

      PIVOT XML (  AVG(VAL) FOR(FUND) IN  (SELECT FUND FROM T ))

       

      Expected Output

      IDVWXY
      1232425
      22223

       

      Reference:= These columns must be dynamic as if a new fund enter into system it must automatically show pivoting for these,

                         That's why, I haven't used Case,Decode and Listagg (giving result in single cell).

      Any guidance is appreciated.

        • 2. Re: Dynamic pivoting with subquery
          Stew Ashton

          Here's one possible implementation. In real life, you would create a function that returns a ref cursor. The calling program would use that cursor just as it would the result of a SELECT statement. Warning: the calling program needs to close the cursor explicitly!

           

          variable refcur refcursor;
          set serveroutput on
          declare
            l_sql varchar2(4000) :=
          'select * from t
          pivot(max(val) for fund in(
            #FUNDLIST#
          ))';
            l_fundlist varchar2(4000);
          begin
            select listagg('''' || fund || ''' as ' || fund, ',') within group(order by fund)
            into l_fundlist
            from (select distinct fund from t);
            l_sql := replace(l_sql, '#FUNDLIST#', l_fundlist);
            dbms_output.put_line(l_sql);
            open :refcur for l_sql;
          end;
          /
          print :refcur
          
          

           

          select * from t pivot(max(val) for fund in(

            'V' as V,'W' as W,'X' as X,'Y' as Y

          ))

           

          IDVWXY
          1232425
          22223
          • 3. Re: Dynamic pivoting with subquery
            Karthick_Arp

            > Here's one possible implementation. In real life, you would create a function that returns a ref cursor. The calling program would use that cursor just as it would the result of a SELECT statement. Warning: the calling program needs to close the cursor explicitly!

             

            Just wondering how the calling program will handle a REFCURSOR with unknown projection

            • 4. Re: Dynamic pivoting with subquery
              Prashant Dabral

              Hi Stew,

               

              I've already tried but here my user wants a query that he'll run by own.

              Yeah could also helpful if any stored procedure/function returns that query.

               

              What i was looking to achieve it in a single query. I really appreciate valuable input from you and Karthik.

              • 5. Re: Dynamic pivoting with subquery
                BluShadow

                Karthick_Arp wrote:

                 

                > Here's one possible implementation. In real life, you would create a function that returns a ref cursor. The calling program would use that cursor just as it would the result of a SELECT statement. Warning: the calling program needs to close the cursor explicitly!

                 

                Just wondering how the calling program will handle a REFCURSOR with unknown projection

                 

                Most 3rd party applications know how to handle ref cursors with an unknown projection.

                It's the same as using the DBMS_SQL package in PL/SQL code, you can do a describe of the query to determine the projection columns and datatypes, before fetching the data.

                In 11g onwards you can even turn a ref cursor into a DBMS_SQL cursor to achieve the same if you really want... though better not to use a ref cursor in the first place in that case.

                • 6. Re: Dynamic pivoting with subquery
                  BluShadow

                  Prashant Dabral wrote:

                   

                  Hi Stew,

                   

                  I've already tried but here my user wants a query that he'll run by own.

                  Yeah could also helpful if any stored procedure/function returns that query.

                   

                  What i was looking to achieve it in a single query. I really appreciate valuable input from you and Karthik.

                   

                  Read the link that Karthick provided above.

                  You need to understand that SQL projection needs to be known before any data is fetched.

                  That means that you cannot base the columns returned by a query on the data, because the columns have to be known before and data is fetched.  The only way to achieve that is with dynamic queries that query the data first to determine what columns are required and then dynamically generate a second query to fetch the data into the generated columns.

                   

                  The key thing to remember is that: A dynamic number of columns cannot be (easily) achieve using a single SQL statement.  (I say "easily" because it's possible to do things to achieve it, but typically it's a lot more trouble than it's worth and difficult code to understand).

                  • 7. Re: Dynamic pivoting with subquery
                    Stew Ashton

                    Karthick_Arp wrote:

                     

                    Just wondering how the calling program will handle a REFCURSOR with unknown projection

                    I think there are two aspects of your question. First, can the calling program handle dynamic SQL? Usually yes. JDBC for example uses only dynamic SQL: whether I submit a SELECT or get a REF CURSOR, JDBC gets the column names and data types from the result set and processes the rows based on that. SQL*Plus does the same thing when I "print" a REF CURSOR. (BluShadow already answered this part but I wanted to show my agreement.)

                     

                    The second aspect is on the application level. Suppose JDBC gives me the right data: what can I as an application do with it? If there is a new fund called 'Z' in a new column, will my code know what to do with it?

                     

                    This second question is valid for any pivoting solution, or more generally any solution that can produce a dynamic result.