put results of execute statement into table in MySQL

Question

At MySQL pivot efficiency

I was wondering how to pivot a table of stock prices in MySQL. The consensus seems to be that this is not a good thing to do. I originally wanted each column to be an ID number and each row to be a date. I got much more speed by making each column a date and each row an ID number:

  SET @@group_concat_max_len = 1000000;
  SET @sql = NULL;
  SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(ddate = ',
      'ddate',
      ',closing_price, NULL)) AS ''',
      DATE_FORMAT(ddate,'%Y%m%d'),''''
      )
  ) INTO @sql
  FROM rawdatatable;
  SET @sql = CONCAT('SELECT idnum , ', @sql, ' FROM rawdatatable GROUP BY idnum');
  SELECT @sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;

Is there any way to put the results of the EXECUTE statement into a table, where each column name is a different ddate?

Answer 1

Change this line

SET @sql = CONCAT('SELECT idnum , ', @sql, ' FROM rawdatatable GROUP BY idnum');

to

SET @sql = CONCAT('CREATE TABLE blah AS SELECT idnum , ', @sql, ' FROM rawdatatable GROUP BY idnum');