11 Replies Latest reply: Nov 14, 2012 3:22 PM by Frank Kulash RSS

    dynamic pivoting

    user13667036
      Hello,
      I am hoping to get some help on a view which needs to be pivoted, I am not sure though.
      View is in following format:

      Case CASE_ORDER MANAGER CURRENT_MONTH_CASES FISCAL_YEAR_CASES
      -------------------------------------------------------------------------------
      case_1 1 John 15 84
      case_1 1 Jeff 10 80
      case_2 2 John 20 90
      case_2 2 Jeff 13 65
      case_3 3 John 7 72
      case_3 3 Jeff 17 70

      My final chart should look like the following:

      case case_order John_current_month John_FY Jeff_current_month Jeff_FY
      ------------------------------------------------------------------------------
      case_1 1 15 84 10 80
      case_2 2 20 90 13 65
      case_3 3 7 72 17 70

      My problem is that managers can change and so does the number of managers from month to month,
      so I can't hard code their names (ie. 'John' and 'Jeff') and use DECODE. It has to be dynamic...
      Will really appreciate some help. Thanks in advance.
        • 1. Re: dynamic pivoting
          Solomon Yakobson
          Not possible. If you know max possible number of managers per case, e.g. number of managers per case can't be more than 5:
          with sample_table as (
                                select 'case_1' case,1 case_order,'John' manager,15 current_month_cases,84 current_fiscal_year_cases from dual union all
                                select 'case_1',1,'Jeff',10,80 from dual union all
                                select 'case_2',2,'John',20,90 from dual union all
                                select 'case_2',2,'Jeff',13,65 from dual union all
                                select 'case_3',3,'John',7,72 from dual union all
                                select 'case_3',3,'Jeff',17,70 from dual
                               ),
                          t as (
                                select  s.*,
                                        dense_rank() over(partition by case_order order by manager) rnk
                                  from  sample_table s
                               )
          select  case,
                  case_order,
                  sum(
                      case rnk
                        when 1 then current_month_cases
                      end
                     ) current_month_cases1,
                  sum(
                      case rnk
                        when 1 then current_fiscal_year_cases
                      end
                     ) current_fiscal_year_cases1,
                  sum(
                      case rnk
                        when 2 then current_month_cases
                      end
                     ) current_month_cases2,
                  sum(
                      case rnk
                        when 2 then current_fiscal_year_cases
                      end
                     ) current_fiscal_year_cases2,
                  sum(
                      case rnk
                        when 3 then current_month_cases
                      end
                     ) current_month_cases3,
                  sum(
                      case rnk
                        when 3 then current_fiscal_year_cases
                      end
                     ) current_fiscal_year_cases3,
                  sum(
                      case rnk
                        when 4 then current_month_cases
                      end
                     ) current_month_cases4,
                  sum(
                      case rnk
                        when 4 then current_fiscal_year_cases
                      end
                     ) current_fiscal_year_cases4,
                  sum(
                      case rnk
                        when 5 then current_month_cases
                      end
                     ) current_month_cases5,
                  sum(
                      case rnk
                        when 5 then current_fiscal_year_cases
                      end
                     ) current_fiscal_year_cases5
            from  t
            group by case,
                     case_order
            order by case,
                     case_order
          /
          
          CASE   CASE_ORDER CURRENT_MONTH_CASES1 CURRENT_FISCAL_YEAR_CASES1 CURRENT_MONTH_CASES2 CURRENT_FISCAL_YEAR_CASES2
          ------ ---------- -------------------- -------------------------- -------------------- --------------------------
          CURRENT_MONTH_CASES3 CURRENT_FISCAL_YEAR_CASES3 CURRENT_MONTH_CASES4 CURRENT_FISCAL_YEAR_CASES4 CURRENT_MONTH_CASES5
          -------------------- -------------------------- -------------------- -------------------------- --------------------
          CURRENT_FISCAL_YEAR_CASES5
          --------------------------
          case_1          1                   10                         80                   15                         84
          
          
          
          case_2          2                   13                         65                   20                         90
          
          
          
          CASE   CASE_ORDER CURRENT_MONTH_CASES1 CURRENT_FISCAL_YEAR_CASES1 CURRENT_MONTH_CASES2 CURRENT_FISCAL_YEAR_CASES2
          ------ ---------- -------------------- -------------------------- -------------------- --------------------------
          CURRENT_MONTH_CASES3 CURRENT_FISCAL_YEAR_CASES3 CURRENT_MONTH_CASES4 CURRENT_FISCAL_YEAR_CASES4 CURRENT_MONTH_CASES5
          -------------------- -------------------------- -------------------- -------------------------- --------------------
          CURRENT_FISCAL_YEAR_CASES5
          --------------------------
          
          case_3          3                   17                         70                    7                         72
          
          
          
          
          SQL> 
          SY.
          • 2. Re: dynamic pivoting
            Frank Kulash
            Hi,

            See {message:id=3527823} for several options.
            String aggregation seems like the best choice for this problem.
            • 3. Re: dynamic pivoting
              user13667036
              SY, thanks again for your reply. The table I gave is a sample one, the actual table is too big and number of distinct managers can be more than 5 for each case. Also as I said in my original post, managers' names can change from time to time so I can't hard code the names (in fact currently I have the hard-coded names and I change it when required). But since you are saying manager numbers can't be more than 5 then I think I am stuck and have to update the hard coded values when necessary. As suggested in the next reply I will try to see if string aggregation can be of any help.
              • 4. Re: dynamic pivoting
                708388
                What about creating a small table with Manager Name and Pivot Number, then join it to your table by manager name, that way you may not need to hard code the pivot, only update the names in this table and use the field MGR1, MGR2, etc:
                MGR_PIVOT   MGR_NAME    
                MGR1            Jeff
                MGR2            Eric
                MGR3            Nancy
                MGR4            Rachel
                MGR5            Bill
                Edited by: user1069723 on Nov 7, 2012 8:47 AM
                • 5. Re: dynamic pivoting
                  Keith Jamieson
                  There is an example in Tom Kytes Expert one -on-one in which he creates a package which he uses to create a dynamic pivot.

                  Pg 580 Wrox Press 2001.

                  (note : Wrox press went out of business but I think someone else may have picked up the Title since).

                  Its in Chapter 12 on Analytical functions.
                  • 6. Re: dynamic pivoting
                    user13667036
                    Thanks for your suggestion, I figured it out. In fact there is a similar answer in tom kyte's blog (http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42asktom-1653097.html) which I modified for my purpose. Here it is:

                    CREATE OR REPLACE PROCEDURE dynamic_pivot_proc ( p_cursor IN OUT SYS_REFCURSOR )
                    AS
                    l_query LONG := 'SELECT case_order, case';
                    BEGIN
                    FOR x IN (SELECT DISTINCT manager FROM test_table ORDER BY 1 )
                    LOOP
                    l_query := l_query ||
                    REPLACE( q'|, MAX(DECODE(manager,'$X$',monthly_total)) $X$_current_month|',
                    '$X$', dbms_assert.simple_sql_name(x.manager) ) ||
                    REPLACE( q'|, MAX(DECODE(manager,'$X$',fiscal_total)) $X$_fy|',
                    '$X$', dbms_assert.simple_sql_name(x.manager) );

                    END LOOP;

                    l_query := l_query || ' FROM test_table
                    GROUP BY case_order, case
                    ORDER BY case_order ';

                    OPEN p_cursor FOR l_query;
                    END;

                    SQL> variable x refcursor;
                    SQL> exec dynamic_pivot_proc( : x );
                    SQL> print x

                    CASE CASE_ORDER JEFF_CURRENT_MONTH JEFF_FY JOHN_CURRENT_MONTH JOHN_FY
                    ----------------------------------------------------------------------------------------------------------------------------------------------
                    1 case_1 10 80 15 84
                    2 case_2 13 65 20 90
                    3 case_3 17 70 7 72


                    Now the thing is instead of printing the result I want to store it in a view. How do I achieve that? I tried to modify the line
                    l_query LONG := 'SELECT case_order, case';
                    with
                    l_query LONG := 'CREATE OR REPLACE VIEW SELECT case_order, case';
                    obviously it did not work because CREATE OR REPLACE is a DDL statement, so some how I have to use EXECUTE IMMEDIATE.

                    Any suggestion? Thanks in advance.

                    Edited by: user13667036 on Nov 14, 2012 8:11 AM
                    • 7. Re: dynamic pivoting
                      Frank Kulash
                      Hi,
                      user13667036 wrote:
                      ... Now the thing is instead of printing the result I want to store it in a view. How do I achieve that? I tried to modify the line
                      l_query LONG := 'SELECT case_order, case';
                      with
                      l_query LONG := 'CREATE OR REPLACE VIEW SELECT case_order, case';
                      obviously it did not work because CREATE OR REPLACE is a DDL statement, so some how I have to use EXECUTE IMMEDIATE.
                      Is there any reason why you need to create views in PL/SQL?

                      Assuming you really want to create views in PL/SQL, then, as you said, you need EXECUTE IMMEDIATE. Just store the complete text of the CREATE VIEW statement in a variable (such as l_query), and then use:
                      EXECUTE IMMEDIATE  l_query; 
                      If you have trouble, post a complete script that re-creates the problem.

                      Don't use the LONG datatype. In PL/SQL, VARCHAR2 variables can hold up to 32767 bytes. If you need more than that, use CLOBs.
                      • 8. Re: dynamic pivoting
                        user13667036
                        The reason I want to store the data in a view is because I want to use those info for other query. Regarding your hint, sorry, I did not quite follow, however I tried like this:
                        1        CREATE OR REPLACE PROCEDURE dynamic_pivot_proc ( p_cursor IN OUT SYS_REFCURSOR )  
                        2        AS  
                        3        l_query VARCHAR2(4000) := 'CREATE OR REPLACE VIEW test_vw AS SELECT case_order, case';   
                        4        BEGIN  
                        5        FOR x IN (SELECT DISTINCT manager FROM test_table ORDER BY 1 )  
                        6        LOOP  
                        7            l_query := l_query ||  
                        8            REPLACE( q'|, MAX(DECODE(manager,'$X$',monthly_total)) $X$_current_month|',   
                        9                          '$X$', dbms_assert.simple_sql_name(x.manager) ) ||  
                        10          REPLACE( q'|, MAX(DECODE(manager,'$X$',fiscal_total)) $X$_fy|',   
                        11                        '$X$', dbms_assert.simple_sql_name(x.manager) );  
                        12      END LOOP;  
                        13      l_query := l_query || ' FROM test_table 
                        14                                    GROUP BY case_order, case
                        15                                    ORDER BY case_order ';
                        16     OPEN p_cursor FOR l_query;
                        17     EXECUTE IMMEDIATE l_query;
                        18     END; 
                        and ended up with the following error:

                        SQL>variable x refcursor;
                        SQL>execute pivot_test_proc(:x);

                        Error starting at line 2 in command:
                        execute pivot_test_proc(:x)
                        Error report:
                        ORA-00922: missing or invalid option
                        ORA-06512: at "DYNAMIC_PIVOT_PROC", line 19
                        ORA-06512: at line 1
                        00922. 00000 - "missing or invalid option"
                        *Cause:   
                        *Action:


                        Can you please suggest where I am making mistake?
                        • 9. Re: dynamic pivoting
                          6363
                          user13667036 wrote:
                          16     OPEN p_cursor FOR l_query;
                          17     EXECUTE IMMEDIATE l_query;
                          18     END; 
                          I am not sure because the error refers to line 19 but the procedure does not have a line 19.

                          But the OPEN on line 16 expects a query that returns results, and l_query contains create view DDL statement so that will not work.

                          For other potential issues use dbms_output.put_line to output the string l_query and validate you can run it outside of the procedure.
                          • 10. Re: dynamic pivoting
                            user13667036
                            Sorry, it should be line 16. When I pasted the code I removed some empty lines.
                            • 11. Re: dynamic pivoting
                              Frank Kulash
                              Hi,
                              user13667036 wrote:
                              The reason I want to store the data in a view is because I want to use those info for other query.
                              Regarding your hint, sorry, I did not quite follow, however I tried like this:
                              1        CREATE OR REPLACE PROCEDURE dynamic_pivot_proc ( p_cursor IN OUT SYS_REFCURSOR )  
                              2        AS  
                              3        l_query VARCHAR2(4000) := 'CREATE OR REPLACE VIEW test_vw AS SELECT case_order, case';   
                              4        BEGIN  
                              5        FOR x IN (SELECT DISTINCT manager FROM test_table ORDER BY 1 )  
                              6        LOOP  
                              7            l_query := l_query ||  
                              8            REPLACE( q'|, MAX(DECODE(manager,'$X$',monthly_total)) $X$_current_month|',   
                              9                          '$X$', dbms_assert.simple_sql_name(x.manager) ) ||  
                              10          REPLACE( q'|, MAX(DECODE(manager,'$X$',fiscal_total)) $X$_fy|',   
                              11                        '$X$', dbms_assert.simple_sql_name(x.manager) );  
                              12      END LOOP;  
                              13      l_query := l_query || ' FROM test_table 
                              14                                    GROUP BY case_order, case
                              15                                    ORDER BY case_order ';
                              16     OPEN p_cursor FOR l_query;
                              17     EXECUTE IMMEDIATE l_query;
                              What is l_query supposed to be?
                              Is it a CREATE VIEW statement? If so, you can't OPEN it. if you want to create a view, and then query that view, then you need to create the view first, and then query it.

                              Whenever you do ny dynamic SQL, it's a good idea to display the code before you try to execute it. For example
                              dbms_output.put_line (l_query || ' = l_query right before EXECUTE IMMEDIATE');
                              -- EXECUTE IMMEDIATE  l_query;
                              Whn you're sure that l_query is being formed correctly, then you can un-comment the EXECUTE IMMEDIATE statement.
                              18     END; 
                              and ended up with the following error:

                              SQL>variable x refcursor;
                              SQL>execute pivot_test_proc(:x);

                              Error starting at line 2 in command:
                              execute pivot_test_proc(:x)
                              Error report:
                              ORA-00922: missing or invalid option
                              ORA-06512: at "DYNAMIC_PIVOT_PROC", line 19
                              ORA-06512: at line 1
                              00922. 00000 - "missing or invalid option"
                              *Cause:   
                              *Action:


                              Can you please suggest where I am making mistake?
                              The error message says it's on line 19, put you only posted 18 lines. Perhaps you thought that the next line wasn't part of the problem, but the fact is, you always need to post a complete test scriopt that people can run to re-create the problem and test their ideas. That includes CREATE TABLE and INSERT statements for any tables needed, and the code that calls the procedure. Also post the output you want from the given data and code.

                              It's still unclear to me why you need dynamic SQL.
                              it looks like you're trying to create a view that always has 1 row, but the number of columns depends on what's in the table at the time you create the view. What are you going to do with those results? Are you going to pass them to some other procedure, which will then analyze the numbers? Why not pass that other procedure a cursor that produces a variable number of rows, but a fixed number of columns? That's how SQL was designed to work. Are you just going to display the results? Why can't you display a big string, the result of string aggregation?