Hi all,
I would like to propose native PIVOT and UNPIVOT support in IoTDB's table-model
SQL. This follows the request in issue #17799.
https://github.com/apache/iotdb/issues/17799
Today, users need lengthy CASE WHEN aggregates to turn rows into columns, or
UNION ALL queries to turn measurement columns into rows. The proposal provides
both SQL-style clauses and shorter, DuckDB-inspired statements. Both could be
used in top-level queries, CTEs, and subqueries.
PIVOT would aggregate values from a long-format input into columns. For example:
PIVOT device_metrics
ON region IN ('north', 'south')
USING AVG(temperature) AS avg_temp
GROUP BY device_id;
An explicit IN list defines the output columns. Without IN, IoTDB would
discover distinct pivot values from the input. Multiple pivot expressions and
aggregates would be supported; multiple pivot expressions would produce
combinations of their values. If GROUP BY is omitted, input columns not
referenced by the pivot expressions or aggregates would become implicit
grouping columns. In particular, this could include time.
UNPIVOT would expand measurement columns into rows. For example:
UNPIVOT device_metrics
ON temperature, humidity
INTO NAME measurement VALUE value;
It would support explicitly listed columns, COLUMNS(* EXCLUDE (...)) for
schema-based selection, and groups of source columns mapped to multiple value
columns. By default, rows whose resulting value columns are all NULL would be
omitted. SQL-style UNPIVOT would also offer INCLUDE NULLS.
The proposal defines column naming and ordering, type compatibility, and
semantic errors for cases such as duplicate output names or mismatched UNPIVOT
column groups. Static PIVOT should reuse the existing grouped-aggregation path;
UNPIVOT should expand each input row without rescanning the input relation. The
user manual would be updated.
Best regards,
Xinqi Zhao
