I am new to oracle, I am trying to pass the dynamically generated column names to pivot in oracle using the below query
DECLARE
v_cols VARCHAR2(100);
v_query VARCHAR2(4000);
BEGIN
SELECT LISTAGG('''' ||product_code||'''',',') WITHIN GROUP (ORDER BY product_code)
INTO v_cols
FROM (
SELECT DISTINCT product_code
FROM pivot_test
);
v_query:='
SELECT *
FROM (
SELECT product_code,
quantity
FROM pivot_test)
PIVOT (sum(quantity) AS qunts
FOR product_code IN ('|| v_cols ||'));';
EXECUTE IMMEDIATE v_query;
--dbms_output.Put_line(v_cols);
--dbms_output.Put_line(v_query);
END;
The column generated is 'A','B','C','D' and the query generated with dynamic column is
SELECT *
FROM (
SELECT
product_code,
quantity
FROM pivot_test) PIVOT (sum(quantity) AS qunts FOR product_code IN ('A','B','C','D'));
Result:
When I take the above query and run it separately it is running correctly but when I use EXECUTE IMMEDIATE v_query;
I get the error
ORA-00911: invalid character
ORA-06512: at line 20
I am not aware what is causing the problem here can any please point me what is wrong with this dynamic query execution
Value used for testing
CREATE TABLE pivot_test
(
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);
INSERT INTO pivot_test VALUES (1,1,'A',10);
INSERT INTO pivot_test VALUES (2,1,'B',20);
INSERT INTO pivot_test VALUES (3,1,'C',30);
INSERT INTO pivot_test VALUES (4,2,'A',40);
INSERT INTO pivot_test VALUES (5,2,'C',50);
INSERT INTO pivot_test VALUES (6,3,'A',60);
INSERT INTO pivot_test VALUES (7,3,'B',70);
INSERT INTO pivot_test VALUES (8,3,'C',80);
INSERT INTO pivot_test VALUES (9,3,'D',90);
INSERT INTO pivot_test VALUES (10,4,'A',100);
COMMIT;
Copyright Notice:Content Author:「Rajesh」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/60867255/passing-dynamic-columns-to-pivot-in-oracle