Pivoting
Pivoting is a relational data transformation that converts distinct row values into columns, typically aggregating associated values.
DuckDB in Action
A pivot is a relational transformation that turns distinct values from rows into columns, typically while aggregating associated values.
- How it is used
- Reorganizes grouped analytical results into spreadsheet-style columns for reports and downstream consumption.
- How deep it goes
- Defines row-to-column transformation, demonstrates filtered aggregates and dynamic PIVOT, then extends to multiple aggregates.
- Examples
- Uses energy production by system and year, daily power by date, and total-versus-best-day measures.
- What you can do after
- Build pivoted DuckDB reports with filtered aggregates or dynamic PIVOT, including date columns and multiple measures.
100 SQL Server Mistakes and How to Avoid Them
The PIVOT operator transforms grouped row values into columns while applying an aggregation to associated values.
- How it is used
- Produces horizontal reports from grouped relational data, such as stock totals by product category.
- How deep it goes
- Shows the source query, aggregate, and explicit output-column list, while contrasting the result with cursor implementation and cost.
- Examples
- Uses MagicChoc product stock, named category columns, and equivalent cursor and PIVOT implementations.
- What you can do after
- Apply it via T-SQL PIVOT to build grouped horizontal reports without row-by-row cursor updates.
Read how 100 SQL Server Mistakes and How to Avoid Them explains pivoting →
Related concepts
- cursoralternative to · A database cursor is an object or interface that executes database operations and incrementally retrieves or traverses their result rows.
- pivot on clausehas part · A pivot ON clause specifies the expression whose distinct values become the columns of a pivoted result.
- filtered aggregateused by · A filtered aggregate is an aggregate expression that incorporates only rows satisfying a specified filter condition.
- pivot using clausehas part · A pivot USING clause specifies the aggregate expressions that compute the values in pivoted cells.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.