0

I'm trying to get a Dynamic PIVOT query in 12c working but am struggling to find the correct solution. In this case weeks 40-43 would be columns but as the month changes so will the weeks.

PIVOT XML works with the data but obviously in an XML format, not acceptable, so I'm looking for some alternative solutions and ideas????

Thanks,

The report is period/week based (stripped down here) 

SELECT STORE, NET_SALES, WEEK
FROM Table A

PIVOT
(
SUM(NET_SALES)
FOR WEEK IN (40,41,42,43)
)
ORDER BY STORE;


Subquery for IN Clause…

SELECT DISTINCT(CT.WK_OF_YEAR)
FROM CALENDAR CT
WHERE CT.PERIOD_NO = '10'
and CT.THEYEAR = '2017'
ORDER BY CT.WK_OF_YEAR
Adam M
  • 109
  • 1
  • 7

1 Answers1

0

Use a procedure to return a REF CURSOR dynamically. . 

CREATE OR REPLACE PROCEDURE pr_getdyn_sale 
                                          (       p_cur OUT SYS_REFCURSOR ) 
IS
        v_in_variables VARCHAR2(200);
BEGIN
        SELECT DISTINCT
                LISTAGG(wk_of_year, ',') WITHIN GROUP( ORDER BY wk_of_year )
        INTO    v_in_variables
        FROM
                ( SELECT DISTINCT
                        ( ct.wk_of_year )
                FROM
                        calendar ct
                WHERE   ct.period_no   = '10'
                        AND ct.theyear = '2017'
                );

        OPEN p_cur FOR 'SELECT *    
FROM TableA    

PIVOT    
(    
SUM(NET_SALES)    
FOR WEEK IN (' || 
        v_in_variables                                                                                        || 
        ')    
)    
ORDER BY STORE';
END;
/

You can now execute the procedure and get the results using this call.

variable x refcursor
exec pr_getdyn_sale( :x )
print x

This works in sqlplus / sqldeveloper prompt and not inside PL/SQL blocks.

If you are using Oracle 12c, you can avoid the procedure with REF CURSOR argument by using DBMS_SQL.return_result (l_cursor) within PL/SQL, which gives you the query result directly.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Thanks for the post. I've never used an array before so I have a few follow ups and issues I'm running into... PLS-00201: identifier 'A_COLLECTION' must be declared So I declared the array but ran into another issue DECLARE v_in_variables VARCHAR2(200); TYPE a_collection IS VARRAY(6) OF VARCHAR2(200); PLS-00321: expression 'A_COLLECTION' is inappropriate as the left hand side of an assignment statement – Adam M Nov 10 '17 at 14:20
  • If I just declare the variable: DECLARE v_in_variables VARCHAR2(200); a_collection VARCHAR2(200); I get the following error: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list – Adam M Nov 10 '17 at 14:38
  • Anyone able to assist with this? – Adam M Nov 12 '17 at 18:51
  • @AdamM : I have modified it to use a different logic with `REF cursor`. Check and let me know if it works for you. – Kaushik Nayak Nov 13 '17 at 04:43
  • Thanks for that. 1 follow up question... if using the PIVOT XML functionality is it possible to parse the output to get the result directly? – Adam M Nov 14 '17 at 13:30
  • @AdamM : Could you please ask that as a separate question?. This answer has solution for the expected result and you may try this if you like. Please do upvote or mark it as right if you found so. – Kaushik Nayak Nov 14 '17 at 14:00