Pivot Tables

`pivot` — wide format

Pivot a long table into wide format by spreading a column's values across multiple columns.

with sales as pivot_table
    pivot
        rows product
        cols region
        agg sum amount

This creates a table where each unique value of region becomes a column, and each row is a product, with sum(amount) as the values.

with sales as summary
    pivot
        rows category
        cols quarter
        agg sum revenue, mean quantity

with sales as detailed
    pivot
        rows product, category
        cols region, quarter
        agg sum sales, mean profit

Option	Description
`agg <fn> <col>`	Aggregation function and value column
`rows <cols>`	Columns to use as row index
`cols <cols>`	Column whose values become column headers

Convert wide-format data (multiple value columns) to long format (single value column with a variable identifier).

Specify the ID column(s) to keep fixed:

with monthly_sales as long
    unpivot
        id region

All other columns become rows, with a generated variable and value column.

with monthly_sales as long
    unpivot
        id region
        cols jan, feb, mar, apr

Columns not listed in id or cols are dropped.

with monthly_sales as long
    unpivot
        id region
        cols jan, feb, mar
        variable "month"
        value "amount"

with sales as long
    unpivot
        id region, year
        cols q1, q2, q3, q4
        variable "quarter"
        value "revenue"

Option	Description
`id <cols>`	Columns to keep as identifiers (required)
`cols <cols>`	Columns to unpivot (optional; default = all non-id columns)
`variable "name"`	Name for the variable column (default `"variable"`)
`value "name"`	Name for the value column (default `"value"`)