Julian Hyde created CALCITE-3752: ------------------------------------ Summary: PIVOT and UNPIVOT Key: CALCITE-3752 URL: https://issues.apache.org/jira/browse/CALCITE-3752 Project: Calcite Issue Type: Bug Reporter: Julian Hyde
Oracle SQL has PIVOT and UNPIVOT operators for cross-tab support. For [example|https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1], {noformat} SELECT * FROM (SELECT customer_id, product_code, quantity FROM pivot_test) PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c)) ORDER BY customer_id; CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY ----------- -------------- -------------- -------------- 1 10 20 30 2 40 50 3 60 70 80 4 100 4 rows selected. {noformat} In Calcite we could implement this as a prepare-time rewrite, something like this: {noformat} SELECT customer_id, SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity, SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity, SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity FROM pivot_test GROUP BY customer_id ORDER BY customer_id; {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)