liveBook · Manning

Pivot using clause

As explained in DuckDB in Action

A pivot USING clause specifies the aggregate expressions that compute the values in pivoted cells.

A PIVOT USING clause supplies the aggregate expressions that calculate each pivoted cell. For example, USING sum(kWh) turns daily power records into yearly totals for each system, while non-pivot columns such as system_id remain grouping keys. section 4.7

Yearly power totals

PIVOT (FROM v_power_per_day) ON year(day) USING sum(kWh); section 4.7

The clause can apply different aggregates at once. ROUND(SUM(kWh)) can produce a total, while MAX(kWh) identifies the best day; aliases become output-column suffixes such as 2019_total and 2019_best_day. section 4.7

Listing 4.26 Using DuckDB’s PIVOT statement
PIVOT (FROM v_power_per_day) #1
ON year(day)  #2
USING sum(kWh);  #3

Run the compact DuckDB PIVOT example and inspect how the USING aggregate creates one value per system-and-year cell. section 4.7

How DuckDB in Action covers it

How deep it goes
Explains aggregate choice, grouping-key behavior, aliases, and multiple aggregates, but does not detail aggregate implementation internals.
Examples
Demonstrates yearly sums, filtered static pivots, day-level first values, and combined yearly totals with maximum daily values.
What it assumes
Assumes familiarity with SELECT queries and aggregate functions while demonstrating pivot grouping behavior through power-data examples.
What you can do after
Apply it via DuckDB PIVOT syntax to build summarized tables from power records.
How it connects
Connects PIVOT with aggregate functions, grouping keys, filtering, and multiple pivot columns.
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.