Skip to content

Instantly share code, notes, and snippets.

@mikeschinkel
Last active February 16, 2024 01:10
Show Gist options
  • Save mikeschinkel/0348b4a6b1b1a43eea17e28d9e6a2d3b to your computer and use it in GitHub Desktop.
Save mikeschinkel/0348b4a6b1b1a43eea17e28d9e6a2d3b to your computer and use it in GitHub Desktop.
How to transpose a table in Sqlite
-- Example of how to transpose a table in Sqlite
-- This script assumes:
-- Table to transpose named `data`
-- All columns are of type INTEGER
-- Creates several temporary SQL tables in /tmp
-- Generates a transposed table named `transposed`
-- Includes debugging output that can be commented out or removed
-- Create example table to transpose
DROP TABLE IF EXISTS data;
CREATE TEMP TABLE data (
A INTEGER,
B INTEGER,
C INTEGER,
D INTEGER,
E INTEGER
);
INSERT INTO data (A, B, C, D, E) VALUES (1,2,3,4,5);
INSERT INTO data (A, B, C, D, E) VALUES (7,8,9,10,11);
-- Set modes for generating dynamic SQL
.mode list
.header off
-- Generate and run code that creates a one-row table with
-- both DDL and DML snippets for the columns, e.g.
-- `var1 INTEGER, var2 INTEGER`
.output /tmp/columns.sql
SELECT 'DROP TABLE IF EXISTS columns;';
SELECT 'CREATE TABLE columns (ddl TEXT, dml TEXT);';
SELECT 'INSERT INTO columns (ddl,dml) SELECT ';
SELECT " group_concat('val'||rowid||' INTEGER',',') AS ddl,";
SELECT " group_concat('val'||rowid,',') AS dml ";
SELECT 'FROM data ORDER BY rowid;';
SELECT '--SELECT * FROM columns;';
.read /tmp/columns.sql
.output
SELECT * FROM columns;
-- Generate code that creates the insert statements for the
-- transposed table. This requires two levels of generation
-- hence inserts1.sql and inserts2.sql
.output /tmp/inserts1.sql
SELECT
printf('SELECT "INSERT INTO transposed (Item,%s) VALUES (%s,"||(SELECT group_concat(%s,'','') FROM data)||");" ' ||
'FROM pragma_table_xinfo(''data'') WHERE name=%s;',
columns.dml,
quote(name),
name,
quote(name)) AS statement
FROM pragma_table_xinfo('data')
JOIN columns ON 1=1;
.output /tmp/inserts2.sql
.read /tmp/inserts1.sql
-- Generate and run code that creates the transposed table.
.output /tmp/table.sql
SELECT 'DROP TABLE IF EXISTS transposed;';
SELECT 'CREATE TEMPORARY TABLE transposed (Item TEXT,'||ddl||');' FROM columns;
.read /tmp/table.sql
-- Generate and run the inserts for the transposed table.
.read /tmp/inserts2.sql
-- Debugging output
.output
.schema transposed
SELECT * FROM transposed;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment