Create a table with column names derived from the row values ​​of another table

Question

Suppose I have the following table with one column:

Table_1

----------- | nameCol | ----------- | A | | A | | B | | C | ----------- 

And I want to create a new table with the following column names:

Table 2

 | pk | A | B | C | 

That is, data from one table becomes the column names of the second table. There may be a vault at some level, but I cannot get an answer.

I tried:

 create table Table_2 ( select group_concat(distinct(nameCol), " varchar(50), ") from Table_1 ); 

Answer 1

You can use a dynamic query:

 SELECT CONCAT( 'CREATE TABLE Table_2 (', GROUP_CONCAT(DISTINCT CONCAT(nameCol, ' VARCHAR(50)') SEPARATOR ','), ');') FROM Table_1 INTO @sql; PREPARE stmt FROM @sql; EXECUTE stmt; 

See the fiddle here .