create table and assign table names from a select query in sqlite

Question

I have a select query that returns a single column. Is there a way in sqlite to create a new table using the results as column names?

I tried this but it did not work.

CREATE TABLE newTable (SELECT nameCol FROM oldTable);

Answer 1

SQLite does not support dynamic SQL so this is not possible.

The best that you can do is construct the SQL statement that you can use to create the table by using your preferred programming language:

SELECT 'CREATE TABLE newTable (' ||
       (SELECT GROUP_CONCAT(nameCol) FROM oldTable) ||
       ');' AS sql;

The above query returns 1 row with 1 column with a string like:

CREATE TABLE newTable (column1,column2,column3);

See a simplified demo.