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;
This works:
SELECT *
FROM (SELECT customer_id, product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A', 'B', 'C', 'D'))
ORDER BY customer_id;
But this gives error:
SELECT *
FROM (SELECT customer_id, product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code FROM pivot_test))
ORDER BY customer_id;
Error is: ORA-00936: missing expression
Why can't we give a sub-query for the columns?