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. value: is required, along with at least one of row: and column: - a rows-only pivot is a handy grouped summary:

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.
`result:N`	Optional. The result set the pivot belongs to, counting from 1. Leave it out for the first result.

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;

When a batch returns several result sets, result: puts the pivot on a later one. Here the pivot belongs to the second SELECT:

-- sqlly pivot: row:Region; value:Total:sum; result:2
SELECT COUNT(*) FROM sales;
SELECT Region, Total FROM sales;

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.

The same cross-tab is available interactively without touching the query - see Results › Pivot. Drag fields there until it looks right, then use Save configuration to write the matching directive into your query. The directive applies to the first result set of the run unless it names another with result:N. The lock writes the pivot, with its result:, for you.