SQLlyGrid
functionalWrite the column order, hidden and frozen columns, sort, grouping, and filters into the query.
The grid directive writes the result grid's column arrangement into the query: which columns come first, which are hidden or frozen, how rows are sorted, grouped, and filtered, and which foreign keys show a readable label. It comes back on every run, for anyone who opens the file.
Syntax
-- sqlly grid: <setting>; <setting>…
-- sqlly grid <n>: <setting>; <setting>…Settings are separated by ;, in any order. Without a number the line applies to every result set of the run, like lines:. With one, grid 2: applies only to the second result set, counting from 1.
| Setting | Meaning | Example |
|---|---|---|
order=a, b, c | Display order. The listed columns come first, in this order; the other visible columns follow in their natural order. | order=Id, Name, Total |
hide=a, b | Hide these columns. | hide=Notes, RowVersion |
freeze=N | Pin the first N displayed columns while you scroll sideways. | freeze=1 |
sort=col [asc|desc], … | Sort by one or more columns. asc is the default. | sort=Total desc, Name |
group=col | Group rows by this column. | group=Region |
filter=col op value | A per-column filter. Repeat the setting to filter more columns. | filter=Status is Open |
label=col:display | Foreign-key label: show display from the referenced table next to col. label=col:off turns the label off. | label=CustomerId:Name |
Filter operators
A filter= uses the same conditions as the grid's per-column filter panel:
| Values | Operators | Example |
|---|---|---|
| Text | contains, not contains, begins with, ends with, is, is not, matches (a regular expression) | filter=Email ends with @example.com |
| Numbers and dates | =, !=, >, >=, <, <=, between A and B, not between A and B | filter=Total between 100 and 500 |
| A list of values (the filter panel's checkboxes) | in A | B | C | filter=Region in West | North |
Column names
Write a column name in double quotes when it contains a space, a comma, a semicolon, or a quote, and double a quote inside it: "Order Date", "Rep ""Ace"" Name".
Examples
-- sqlly grid: order=Region, Rep, Total; hide=Notes; freeze=1; sort=Total desc, Rep
-- sqlly grid: filter=Region is West; filter=Total > 1000
SELECT Region, Rep, Total, Created, Notes FROM sales;Two result sets, each with its own arrangement:
-- sqlly grid 1: sort="Order Date" desc; label=CustomerId:CompanyName
-- sqlly grid 2: group=Category; hide=Discontinued
SELECT OrderId, CustomerId, OrderDate AS [Order Date] FROM dbo.Orders;
SELECT ProductName, Category, Discontinued FROM dbo.ProductList;Which arrangement wins
SQLly already remembers how you left a result's columns, per table or per query. A
grid: directive wins over that remembered layout, so the query shows the same
arrangement for everyone who runs it. Changing the grid by hand still works for the moment;
the directive applies again on the next run.
grid: line, with the widths and formats too. See Lock the grid.