Pivoting reorganizes grouped rows so distinct values become columns, usually while aggregating associated measures. It turns a system-and-year result into a spreadsheet-style report with one row per system and one column per year. section 4.7
Static and dynamic energy pivots
A portable static form hardcodes the desired columns: SELECT system_id, sum(kWh) FILTER (WHERE year(day) = 2019) AS 'kWh in 2019', sum(kWh) FILTER (WHERE year(day) = 2020) AS 'kWh in 2020' FROM v_power_per_day GROUP BY system_id; DuckDB can generate columns dynamically instead: PIVOT (FROM v_power_per_day) ON year(day) USING sum(kWh); Both produce one row per system with 2019 and 2020 values; the dynamic form avoids revising the query when years change. section 4.7
Useful variations include:
- Pivot dates into daily columns with first(kWh) after restricting the input range. section 4.7
- Compute several measures per pivot value, such as rounded totals and maximum daily production. section 4.7
- Prefer static filtered aggregates when the column set is fixed or portability to databases without pivoting matters. section 4.7
