Keep pivots, charts and formatting in your SQL

You spend ten minutes getting a result to look right. The sales figures pivoted by region and year, the totals at two decimal places, a bar chart next to it that finally tells the story. Then you save the .sql file, send it to a colleague, and they open it to find a plain, flat grid. All of that presentation lived in your tool's UI state, and none of it travelled with the query.

SQLly's answer is the directive: an ordinary SQL comment that SQLly understands. The database still sees only the query you wrote. SQLly reads the comment and uses it to shape, format or chart the result. Because the directive lives in the query text, it is saved with the file, versioned with the file, and applied again every time anyone runs it.

What a directive looks like

Every directive shares one namespace: the word sqlly right after the comment marker.

-- sqlly view: pivot
-- sqlly pivot: row:Region; column:Year; value:Sales:sum
-- sqlly chart: kind:bar; label:Region; values:Sales
-- sqlly format: column=Sales; number=2

To every other tool these are just comments, so a query with directives still runs unchanged in your CI scripts, your ORM's raw-SQL escape hatch, or someone else's client. Nothing is added to the SQL that reaches the server.

Pivots from a comment

The pivot directive turns a flat result set into a cross-tab without a wizard. A view: directive opens pivot mode, and a pivot: directive describes the rows, columns and values:

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

Fields are key:value pairs separated by semicolons, in any order. The aggregate can be count, sum, avg, min, max, or first. You can add several row or column fields, and format the value cells with valueformat::

-- 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;

Columns are referenced by name, not by position. An early version used zero-based column indexes, which meant reordering the SELECT list could silently pivot the wrong data. Names survive a reshuffle, and [Region], `Region` and "Region" all resolve to the same column.

Charts that arrive configured

Every result grid has a Chart tab next to Grid and Pivot. The chart directive configures it from the query, and view: chart opens it automatically on run:

-- sqlly view: chart
-- sqlly chart: kind:line; label:Month; values:Revenue,Cost; aggregate:sum; order:value; top:12
SELECT Month, Revenue, Cost FROM monthly;

Only kind: is required. It accepts bar, line, area, scatter, histogram, pie and donut. The optional fields set the label column, the numeric series, an aggregate, the category order, a top-N cap, and whether clicking a mark jumps to the grid rows behind it.

Formatting that sticks to the column

A formatting directive describes how one column should look: numbers, dates, booleans, binary, casing, masking, epoch timestamps, JSON extraction, or a small template.

-- sqlly format: column=Total; number=2
-- sqlly format: column=Created; date=%Y-%m-%d
-- sqlly format: column=IsActive; boolean=Yes/No
-- sqlly format: column=rate; number=1; style=percent
-- sqlly format: column=Card; mask=0/4
SELECT * FROM orders;

Formatting is the one family with strict spelling: it needs exactly -- sqlly format:, with a space after the dashes. Write --sqlly format: and it will not apply, and the editor tells you so. The docs list the formatting directive as in progress, so expect it to keep evolving. The per-type defaults you set in Settings › Results feed the same formatting model.

Laying out several result sets

A script that returns several result sets can arrange them on a canvas declared in comments. One directive sets the board size, and each placement claims a row|column cell for the statement below it, with ranges for spanning:

-- sqlly 2x4
-- sqlly result: 1|1-4
SELECT ...;   -- full-width summary row
-- sqlly result: 2|1-2
SELECT ...;   -- bottom-left half

Anything you do not place is auto-placed into the next open cell. It is a small dashboard that lives in a plain .sql file.

You rarely have to type them

Directives would be a chore if you had to memorize the grammar. In practice the UI writes them for you:

• Build a pivot by dragging fields in the Pivot tab, and saving writes the pivot: directive into your query. A layout with only rows, or only columns, round-trips too.
• Tune a chart in the Chart sidebar, and saving writes the chart: directive back.
• Right-click a column header and choose Set Formatting Override… to get a format: comment.
• Typing in a comment offers directive completions, so a partial -- sqlly pivot: can become a full template.

A safety net for typos

The failure mode of any comment-based syntax is silence: you misspell something and nothing happens. SQLly avoids that. One grammar is shared by the editor hover, the validation warnings, autocomplete and the result parsers, so they cannot disagree about what counts as a directive. And a comment in the sqlly namespace that is misspelled, placed where it cannot apply, or given an unknown value is flagged in the editor. For example:

• A view: directive that is not on the first non-empty line of the query, the only place it works.
• An unknown directive keyword, or an unknown chart kind:.
• A directive written after SQL code on the same line, which is ignored.

Hovering a directive explains what it does, and Help › Formatting Directives… opens a searchable reference with copy-ready examples. When several valid hints of the same kind exist, the last one wins, which is predictable enough to reason about.

Why comments, and not a sidecar file?

A sidecar settings file can drift from the query it describes, gets forgotten in a copy-paste, and means nothing to anyone who does not use the tool. A comment sits right next to the SQL it shapes, shows up in code review, and is harmless everywhere else. When the presentation is part of the answer, keeping it in the query is the simplest way to make sure the answer arrives intact.

The full grammar for each family is in the docs under SQLly directives.