How to transpose a table in Sqlite

_readme.md

Created to answer this question on Reddit.

transpose-example.sql

-- 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;