pivot

Package pivot implements a pivot virtual table — a parametrized cross-tab over three caller-supplied SELECT statements:

CREATE VIRTUAL TABLE p USING pivot(
    'SELECT DISTINCT region FROM sales',                       -- row keys
    'SELECT DISTINCT product, product FROM sales',             -- column keys (value, name)
    'SELECT SUM(units) FROM sales WHERE region=? AND product=?' -- per-cell aggregate
);

Each row in the result represents one row-key tuple; each non-key column maps to one distinct column-key value, with the cell value supplied by the cell query. The cell query’s bound parameters are the row-key columns followed by the column-key value.

• args[0] — row-key SELECT. Its columns define the leading (non-pivoted) columns of the vtab.
• args[1] — column-key SELECT. Must return two columns: the bind value the cell query receives (typically the column key) and the display name (used as the vtab’s column name).
• args[2] — cell SELECT. Must return exactly one column and accept (len(rowkeys) + 1) bound parameters.

ncruces/ext/pivot, itself a Go port of the jakethaw/pivot_vtab SQLite extension. We support EQ constraint push-down on row-key columns and currently skip the ORDER BY rewrite.

Full API: pkg.go.dev/gosqlite.org/ext/pivot