Is there a way to transpose a table, including column names, in SQLite?

Question

I want to do basically the opposite of This. I have a table which is like;

property, client, address, postcode, phone_number

This House, John Doe, Here, AB1 1CD, 0123456789

I want to get a table like;

column_name, value

property, This House

client, John Doe

address, Here

postcode, AB1 1CD

phone_number, 0123456789

Ideally i'd like to also be selecting a single row from the first table (which in reality has many rows) and renaming the columns so that they appear in the "column_name" column with their new names.

score 0 · Answer 1 · answered Nov 21 '18 at 18:07

The output table looks like key:value pairs, so how about some JSON? This would accomplish it:

WITH  j1(json_string) as
(select json_object('Prperty',property,'Client',client,'Address',address,
'Postal Code',postcode,'Tel no.',phone_number)
from re
where row = ?
)
insert into coltable
select key,value
from j1,json_each(json_string)

Is there a way to transpose a table, including column names, in SQLite?

1 Answers1