[CALCITE-3752] Add PIVOT operator to SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.26.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 21/Jan/20 21:30
• Updated: 09/Dec/20 23:32
• Resolved: 01/Oct/20 01:10

Description

Oracle SQL has PIVOT and UNPIVOT operators for cross-tab support.

For example,

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.

In Calcite we could implement this as a prepare-time rewrite, something like this:

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;

Issue Links

• is related to: CALCITE-4321 JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
• is related to: CALCITE-4433 Add UNPIVOT operator to SQL
• links to: GitHub Pull Request #2170