[jira] [Commented] (CALCITE-3752) Add PIVOT operator to SQL

["Jeremiah Rhoads Hall (Jira)" <ji...@apache.org>]

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

Jeremiah Rhoads Hall commented on CALCITE-3752:
-----------------------------------------------

Hi folks. I was excited to see this merged and decided to try it out. I found that it works great for the CSV adapter and Reflective schema, but did not get the results I expected when using a JDBC adapter. I dug into it and found that the SQL issued to the database was lacking the `FILTER (WHERE ...)` expressions. Digging further, I found that `FILTER (WHERE ...)` relational expressions appear to be ignored in all cases that I tried by `SqlImplementor.java`.

I have a small fix and test case locally. Should I open a separate issue for this?

 

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