Why pivots and charts live in the results grid

It wasn't analysis. It was a commute.

Worth being precise here, because "accountant uses Excel" usually gets imagined as deep analysis: models, scenarios, what-ifs. That is not what he was doing. He pulled a series of journals and pivoted them. That was the whole job — a mechanical reshape from rows into a cross-tab his eyes could scan. Excel wasn't the workshop; it was the taxi between the database and his eyeballs.

And the taxi ran on a meter. Every journal, every client, every week, he rebuilt the same pivot from scratch: drag the fields, set rows and columns, set the value to sum, fix the number format, maybe re-make the chart. Minutes of clicking to reproduce a shape he already knew he wanted. Then again for the next journal.

Then he became the beta tester

When an early SQLly build could pivot a result right in the grid, he became the trial case — our beta tester. Real journals, real clients, real deadlines. That mattered: a pivot that survives a demo dataset proves nothing; a pivot that survives somebody's Monday-morning bookkeeping is a feature.

The first version removed the Excel trip, and it still wasn't good enough. He no longer exported, but he re-set the pivot on every run — drag, configure, format — because the shape of his answer lived in the tool's UI state and evaporated when the window closed. Same clicking, new location.

Directives exist so he never had to reconfigure it

That is where SQLly directives came from. The first reason they exist is so he wouldn't have to reconfigure the pivot ever again. The presentation became part of the query itself, saved as plain SQL comments inside the .sql file:

-- sqlly view: pivot
-- sqlly pivot: row:Account; column:Month; value:Amount:sum; valueformat:decimals=2
SELECT Account, Month, Amount FROM journal_lines;

Two things made this stick:

• He doesn't type them. He dragged the fields once, hit save, and SQLly wrote the directive into the file. Next week he runs the same saved query and it opens already pivoted. The shape is stored with the question, not with the mood of the UI. The chart directive does the same for the chart: configured once, arriving configured on every run after.
• The comments are inert everywhere else. The database still receives only the query. The file still runs in any other tool, in CI, wherever — the directives just look like what they are: comments.

The full grammar is in the docs under SQLly directives, and the feature tour is in Keep pivots, charts and formatting in your SQL.

What actually happened

He removed Excel from the loop. Not "uses it less" — the weekly journals now live in saved .sql files: run, pivoted, charted, done. Excel is presumably still installed on his machine. It just stopped getting opened.

I keep this story around because it's the bar for what goes into SQLly. Not "wouldn't it be nice if", but: a real person, with a real business, changed a workflow he'd been running for years and deleted a step out of it. That's the test a feature has to pass.

The honest footnote

If your spreadsheet time is real modelling — scenarios, forecasts, ten tabs cross-referencing each other — stay in Excel; it's the right tool for that and pivots in a SQL client won't replace it. But if Excel was your pivot taxi, the way it was for him, you can stop paying the fare: the live preview runs the real workbench in your browser, or download SQLly and re-run your own weekly query on it.

FAQ

Do I have to write directive comments by hand? No. Build the pivot or chart by dragging in the results tabs, save, and SQLly writes the directive into your query. Typing in a comment also offers completions.

Do directives change the SQL that runs on my database? No. They are ordinary comments. The server only ever sees your query; SQLly reads the comments client-side to shape, format and chart the result.

Does this work on any database? Yes. Directives shape results in SQLly after the rows come back, so they work the same on all 30 supported engines.