Pivot

functional

Build 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.

SQLly
SQLly interactive pivot builder with drop zonesSQLly interactive pivot builder with drop zones
The interactive pivot: source fields and the Rows / Columns / Values / Filters drop zones, with the cross-tab rendered alongside.

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):

Region123Grand Total
Central41,20044,95039,800125,950
East58,30061,02063,410182,730
West72,14069,88075,600217,620
Grand Total171,640175,850178,810526,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.
Wait for the full result. Pivot stays locked while a result set is streaming so it never cross-tabs a partial answer. Once the rows finish arriving, the tab unlocks.
No row limit. A set big enough to spill to disk pivots like any other, from every row. SQLly reads the file on a background task each time you change the layout, shows Pivoting… with a percentage, and keeps memory flat. Double-clicking a cell still shows the rows behind it: SQLly gathers them in the background (Finding matching rows…) and opens them on the Grid tab under the usual banner. On a spilled set the tab is unavailable only while the grid has unsaved edits.

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.