[jira] [Created] (CALCITE-3752) PIVOT and UNPIVOT

["Julian Hyde (Jira)" <ji...@apache.org>]

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)