SQLite (at least as of 3.31) out-of-the-box doesn't have PIVOT, CROSSTAB or any other similar functionality. More generally it doesn't have a means to produce columns dynamically (and the resort is manual creation of CASE expressions to define columns). But there's pivot_vtab virtual table extension which implements a pivot table functionality. I guess it's sort of proof-of-concept implementation, but still can be useful depending on the circumstances.
Here's the long description from the repo (redacted so it fits a snippet):
CREATE VIRTUAL TABLE pivot USING pivot_vtab(
--
-- Pivot table row key query. Defines first column of the pivot table.
-- [...]
-- The first column name in this query will become the name of the pivot table key
-- column. The value of the [...] key column is provided to the pivot query as ?1.
--
(SELECT id r_id -- pivot table key
FROM r),
--
-- Pivot table column definition query. Defines second+ column(s) of the pivot
-- table. This query should return pivot table column key/name pairs.
-- [...]
-- The first column of this query is the pivot column key, and is provided
-- to the pivot query as ?2. The second column of this query is used to name the
-- pivot table columns. This column is required to return unique values.
--
-- Changes to this query can only be propagated by dropping and
-- re-creating the virtual table
--
(SELECT id c_id, -- pivot column key - can be referenced in pivot query as ?2
name -- pivot column name
FROM c),
--
-- Pivot query. This query should define a single value in the pivot table when
-- filtered by the pivot table row key (1?) and a column key (2?)
--
(SELECT val FROM x WHERE r_id = ?1 AND c_id = ?2)
);
While I was playing with it I had a couple of issues:
- with invalid
CREATE VIRTUAL TABLE ... it can crash with segmentation fault
(e.g. on missing table)
- the only error message you get out of it is
vtable constructor failed: to_be_table
- it expects separate table with unique pivot column id-name pairs (were character case
makes a difference), and wouldn't create a pivot otherwise
I'll use this GitHub Gist with a CSV with salary data.
Here I prepare the data. Import the CSV into :memory: SQLite3 database, load the extension and
clean the data (I load the records of software engineers at FAANG into a temporary table).
.mode csv
.import salaries.csv salary_import
.load ./pivot_vtab
.headers on
.mode column
CREATE TABLE temp.salary AS
WITH clean AS (
SELECT
Employer employer,
lower(trim("Job Title")) title,
replace("Annual Base Pay", ',', '') base_pay
FROM salary_import
)
SELECT employer, title, round(avg(base_pay)) avg_base_pay, COUNT(*) count
FROM clean
WHERE
employer IN ('Facebook', 'Amazon', 'Apple', 'Netflix', 'Google')
AND title LIKE '%software engineer%'
GROUP BY 1, 2;
And here it's actual pivot creation (it's a temporary table in my example, but it can be persistent just as well).
CREATE VIRTUAL TABLE temp.pivot USING pivot_vtab(
(SELECT employer FROM temp.salary GROUP BY employer),
(SELECT title, title FROM temp.salary GROUP BY title),
(
SELECT avg_base_pay
FROM temp.salary
WHERE employer = ?1 AND title = ?2
)
);
SELECT * FROM temp.pivot;
To run it, save these files in a directory:
salary.sql (the two snippets above combined)salaries.csv (the CSV from the Gist)pivot_vtab.c (the code of the extension)
Then run it like this (chmod o+w . if you run user-namespaced Docker):
$ docker run --rm -it -v $PWD:/tmp/build -w /tmp/build ubuntu:focal
# apt update
# apt install -y --no-install-recommends gcc sqlite3 libsqlite3-dev
# sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc...
# gcc -g -O3 -fPIC -shared pivot_vtab.c -o pivot_vtab.so
# cat salary.sql | sqlite3 --bail
employer data engineer/software engineer senior software engineer software eng...
---------- ------------------------------- ------------------------ ------------...
Amazon 130000.0 ...
Apple 145000.0 155500.0 122667.0 ...
Facebook 182000.0 166690.0 ...
Google 158267.0 131465.0 ...
Netflix 340000.0 ...
