[ https://issues.apache.org/jira/browse/CALCITE-3752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde reassigned CALCITE-3752: ------------------------------------ Assignee: Julian Hyde > PIVOT and UNPIVOT > ----------------- > > Key: CALCITE-3752 > URL: https://issues.apache.org/jira/browse/CALCITE-3752 > Project: Calcite > Issue Type: Bug > Reporter: Julian Hyde > Assignee: Julian Hyde > Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > 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)