Pivot

✓ functional

Turn a familiar result set into a cross-tab with a short, portable comment.

The pivot directive turns a flat result set into a cross-tab without a wizard or separate tool. Use a view directive to open pivot mode and a pivot directive to describe the rows, columns, and values.

Syntax

-- sqlly view: pivot
-- sqlly pivot: row:<cols>; column:<cols>; value:<col>:<agg>

Fields are separated by ; and written as key:value; their order does not matter. These three are required:

Field	Meaning
`row:`	One or more column names (comma-separated) that form the pivot's rows.
`column:`	One or more column names that become the pivot's columns.
`value:col:agg`	The column to aggregate and the aggregate to apply.

Choose count, sum, avg, min, max, or first as the aggregate. Column names are flexible: [Region], `Region`, and "Region" all resolve to the same column.

Examples

-- sqlly view: pivot
-- sqlly pivot: row:Region; column:Year; value:Sales:sum
SELECT Region, Year, Sales FROM sales;

Here is a pivot with two row fields and formatting for its value cells:

-- sqlly view: pivot
-- sqlly pivot: row:Region,Country; column:Year; value:Amount:sum; valueformat:decimals=2, red=true
SELECT Region, Country, Year, Amount FROM ledger;

Optional formatting

Two optional fields format the numbers. valueformat: styles all value cells; format:col: styles one field. Both take either the word default or a comma-separated list of options:

Option	Values
`decimals=`	A non-negative integer - fixed decimal places.
`thousands=`	`true` / `false` - thousands separators.
`red=`	`true` / `false` - show negatives in red.
`parens=`	`true` / `false` - wrap negatives in parentheses.
`align=`	`left` / `center` / `right`.

A SQLly pivot directive and its resulting cross-tab — A pivot directive comment above a query, and the cross-tab it produces in the result grid.

Use standalone comment lines. A view: hint belongs on the first non-empty line of the query; pivot: can appear on any comment line of its own. When several valid pivot hints exist, the last one wins.