Create a table with column names dynamically retrieved from a single-column query result

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 query but it did not work.

CREATE TABLE newTable (SELECT nameCol FROM oldTable);

This sample

CREATE TABLE Foo FROM (SELECT "col_a, col_b, col_c")
-- or this
CREATE Table Foo FROM (SELECT 'col_a, col_b, col_c')

results in the same error

Execution finished with errors. Result: near "FROM": syntax error At line 1: CREATE Table Foo FROM

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.