Module pivot_sql 

E5.4 — PIVOT / UNPIVOT SQL macro rewrite layer.

DataFusion does not parse PIVOT or UNPIVOT natively. This module rewrites those constructs into equivalent standard SQL before passing the query to DataFusion.

PIVOT rewrite

SELECT * FROM sales
PIVOT (SUM(amount) FOR category IN ('food', 'tech', 'clothing'))

becomes:

SELECT
  SUM(CASE WHEN category = 'food' THEN amount END) AS "food",
  SUM(CASE WHEN category = 'tech' THEN amount END) AS "tech",
  SUM(CASE WHEN category = 'clothing' THEN amount END) AS "clothing"
FROM sales

UNPIVOT rewrite

SELECT * FROM monthly
UNPIVOT (value FOR month IN (jan, feb, mar))

becomes a UNION ALL of individual SELECT statements:

SELECT 'jan' AS month, jan AS value FROM monthly
UNION ALL
SELECT 'feb' AS month, feb AS value FROM monthly
UNION ALL
SELECT 'mar' AS month, mar AS value FROM monthly

Structs

PivotClause

Parsed representation of a PIVOT clause.

UnpivotClause

Parsed representation of an UNPIVOT clause.

Functions

contains_pivot

Returns true if sql contains a PIVOT clause (case-insensitive).

contains_unpivot

Returns true if sql contains an UNPIVOT clause (case-insensitive).

parse_pivot

Parse a simple SELECT * FROM <source> PIVOT (<agg>(<col>) FOR <dim> IN (<vals>)) statement.

parse_unpivot

Parse a simple SELECT * FROM <source> UNPIVOT (<val_col> FOR <name_col> IN (<cols>)).

rewrite_pivot

Rewrite a PIVOT statement to equivalent CASE WHEN SQL.

rewrite_pivot_unpivot

Entry point: rewrite PIVOT or UNPIVOT if present, otherwise return unchanged.

rewrite_unpivot

Rewrite an UNPIVOT statement to a UNION ALL of SELECT statements.