[jira] [Created] (CALCITE-4433) Add UNPIVOT operator to SQL

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

Julian Hyde created CALCITE-4433:
------------------------------------

             Summary: Add UNPIVOT operator to SQL
                 Key: CALCITE-4433
                 URL: https://issues.apache.org/jira/browse/CALCITE-4433
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde


Add {{UNPIVOT}} operator to SQL. (We added {{PIVOT}} in CALCITE-3752, and {{UNPIVOT}} is the inverse.)

Oracle has {{UNPIVOT}}. For [example|https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1#unpivot]:
{noformat}
SELECT * FROM unpivot_test;

        ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D
---------- ----------- -------------- -------------- -------------- --------------
         1         101             10             20             30
         2         102             40                            50
         3         103             60             70             80             90
         4         104            100

4 rows selected.

SQL> SELECT *
FROM   unpivot_test
UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));

        ID CUSTOMER_ID P   QUANTITY
---------- ----------- - ----------
         1         101 A         10
         1         101 B         20
         1         101 C         30
         2         102 A         40
         2         102 C         50
         3         103 A         60
         3         103 B         70
         3         103 C         80
         3         103 D         90
         4         104 A        100

10 rows selected.
{noformat}




--
This message was sent by Atlassian Jira
(v8.3.4#803005)