Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4433

Add UNPIVOT operator to SQL

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.27.0
    • Component/s: None
    • Labels:
      None

      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.
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: