You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2020/12/09 23:33:00 UTC
[jira] [Commented] (CALCITE-3752) Add PIVOT operator to SQL
[ https://issues.apache.org/jira/browse/CALCITE-3752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17246895#comment-17246895 ]
Julian Hyde commented on CALCITE-3752:
--------------------------------------
The JDBC adapter issue is CALCITE-4321.
> Add PIVOT operator to SQL
> -------------------------
>
> 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
> Fix For: 1.26.0
>
> Time Spent: 0.5h
> 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)