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

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

    [ https://issues.apache.org/jira/browse/CALCITE-3752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17202441#comment-17202441 ] 

Julian Hyde commented on CALCITE-3752:
--------------------------------------

There is a PR that adds PIVOT support. I'd like to get it into 1.26. Reviews welcome.

> 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)