[CALCITE-4433] Add UNPIVOT operator to SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 09/Dec/20 23:27
• Updated: 03/Jun/21 22:31
• Resolved: 18/Dec/20 22:25

Description

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

Oracle has UNPIVOT. For example:

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.

Issue Links

• relates to: CALCITE-3752 Add PIVOT operator to SQL