Pivot
functionalBuild a cross-tab by moving fields around, then keep the setup as a reusable directive if you like.
Every result grid has a Pivot tab alongside Grid and Chart. Open it to turn a flat result into a cross-tab by moving fields around; the query itself stays untouched.
Building a pivot
The sidebar's Fields section lists every column of the result, each tagged R, C, V, or F once it is in use. The Layout section has four drop zones. Drag a field into the place where it should do its work:
- Rows and Columns - the axes of the cross-tab. Several fields nest, outermost first; drop a field onto a chip to put it in that position.
- Values - the measure to aggregate. The value chip carries a dropdown to pick the aggregate; it reads e.g. “Sum of Amount”. The zone holds one field, so dropping another replaces it.
- Filters - restrict which rows feed the pivot, with a per-field value picker.
A pivot needs a value and at least one Rows or Columns field, a rows-only pivot is a perfectly good grouped summary. The × on a chip takes it back out.
Choose from Count, Sum (the default), Avg, Min, Max, and First. Layout options add subtotals, grand totals, and flat rows; per-field formatting handles decimals, separators, negative values, and alignment.


A walkthrough: sales by region and quarter
Start from flat rows - one per order line, and let the pivot do the grouping:
SELECT r.name AS Region, p.category AS Category, DATEPART(quarter, o.order_date) AS Quarter, o.total AS Amount FROM sales.orders o JOIN sales.regions r ON r.region_id = o.region_id JOIN sales.products p ON p.product_id = o.product_id WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-10-01';
Drag Region to Rows, Quarter to Columns, and Amount to Values. The result is shaped like this (figures illustrative):
| Region | 1 | 2 | 3 | Grand Total |
|---|---|---|---|---|
| Central | 41,200 | 44,950 | 39,800 | 125,950 |
| East | 58,300 | 61,020 | 63,410 | 182,730 |
| West | 72,140 | 69,880 | 75,600 | 217,620 |
| Grand Total | 171,640 | 175,850 | 178,810 | 526,300 |
Now drag Category into Rows below Region: each region becomes a
group with a chevron to collapse it, and its header row carries the region's subtotal.
Switch the aggregate to Avg and every subtotal and total is the true
average of the rows beneath it, not an average of averages. Drop Category into
Filters instead and click its chip to tick only the categories you care about.
Display and export
- Layout - Flat rows (no hierarchy) lays nested row fields out side by side, one row per combination, with no group headers.
- Totals - Row subtotals, Grand total row, and Grand total column start on; Column subtotal columns adds a Total column after each column group.
- Groups - Expand rows, Collapse rows, Expand cols, Collapse cols.
- Export - Copy pivot as CSV.
Double-click any chip in a zone to open its format dialog: Negative numbers in red, Thousands separator, negatives in parentheses, Decimals, and Alignment, with Reset to drop the override.
Drill down
Double-click a pivot cell to return to the Grid tab filtered to the rows behind it. SQLly labels the view “Filtered from pivot” and provides a one-click Clear filter. Right-click a cell for Show source rows in Grid, Copy value, and Copy pivot as CSV; right-clicking a row or column header drills into that whole group.
NULLs and big results
- Groups sort ascending, and a NULL grouping value gets its own (blank) group, listed first.
- Every aggregate skips NULLs, so Count counts non-NULL values. Sum and Avg ignore non-numeric values; Min, Max, and First keep the column's own type, so they work on text and dates.
- The pivot reads the result as returned, the Grid tab's sort and filters do not feed it. Use the Filters zone.
Saving the layout
The disk icon in the Layout header (Save configuration) writes the
pivot into your SQL as a -- sqlly pivot: comment - rows, columns, value,
aggregate, and formats - replacing the last one if there is one. Filters and the totals
switches are not part of it. Put -- sqlly view: pivot on the first line and the
result opens on the Pivot tab each run. Directives apply to the run's first result set.