Create a new table with a column for every element in another table

Question

I have create a table in MySQL where i have all the names of data I'm going to bring in from FRED data.

Now I want to make a new table, where the 1504 names in my example table above, each has one column.

AAA AAA10M AAAFFM ADBJORNS and so on.

So every name in fred_namecol should get one column each with numeric as value. Is there a easy way to do this instead of writing everyone manually?

Maybe there is a way too loop trough each name and make a column for them into a new table?

Answer 1

Yes. There is a way.

In the following query; replace WRITE_YOUR_TABLE_NAME_HERE with the name of the table which contains fred_namecol column:

drop table if exists columns_over_1000;
set @prefix = 'CREATE TABLE columns_over_1000 (';
set @suffix = '\n);';
select @create_table_sql := concat(
    @prefix, 
        group_concat('\n    `', fred_namecol, '` INT'), 
    @suffix) 
from `WRITE_YOUR_TABLE_NAME_HERE`;

PREPARE stmt1 FROM @create_table_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

select * from columns_over_1000;