[
https://issues.apache.org/jira/browse/CALCITE-3752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17020911#comment-17020911
]
Julian Hyde commented on CALCITE-3752:
--------------------------------------
It depends whether this feature is in the SQL standard or other databases, and
I haven't surveyed other databases yet. I am inclined to put in the core
parser, but enabled based on a conformance setting.
> PIVOT and UNPIVOT
> -----------------
>
> Key: CALCITE-3752
> URL: https://issues.apache.org/jira/browse/CALCITE-3752
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> 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)
