liveBook · Manning

Pivot on clause

As explained in DuckDB in Action

A pivot ON clause specifies the expression whose distinct values become the columns of a pivoted result.

A pivot ON clause selects the expression whose distinct values become columns. Other columns form grouping keys when an aggregate computes the cells. section 4.7

Pivot power by year

PIVOT (FROM v_power_per_day) ON year(day) USING sum(kWh); This produces one row per system and columns for 2019 and 2020, containing summed power values. section 4.7

The ON expression also controls the shape and meaning of the result:

  • Use ON day to create a column for each distinct date; without USING, cells are filled with indicators for matching values. section 4.7

How DuckDB in Action covers it

How deep it goes
Explains column generation, grouping keys, aggregates, and default cells, but does not detail broader pivot syntax.
Examples
Demonstrates year, day, filtered date-range, and multi-aggregate pivots over power-per-day data.
What it assumes
Assumes familiarity with SQL expressions, aggregates, grouping, and the PIVOT and USING clauses.
What you can do after
Apply it via DuckDB to build dynamic cross-tabulated results from expressions and aggregates.
How it connects
Connects pivoting with aggregation, grouping, expressions, and the USING clause.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.