Create an insert to pivot value pair values into a table

Question

I have a value pair table that I want to use to create a member table

Based on Taryns answer to this question

MySQL pivot table query with dynamic columns

I have this code that creates selects the data, which works fine

    SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN wpdg_usermeta.meta_key = ''',
      meta_key,
      ''' THEN wpdg_usermeta.meta_value END) `',
      meta_key, '`'
    )
  ) INTO @sql
FROM
wpdg_usermeta
WHERE
wpdg_usermeta.meta_key like "member_%"
;

SET @sql = CONCAT('SELECT user_id, ', @sql, ' 
                  FROM wpdg_usermeta 
                   GROUP BY user_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

So, my question is - does anyone know how I could alter this to run an INSERT instead of a SELECT so that I could populate the new member table?

score 2 · Accepted Answer · answered Aug 31 '20 at 15:52

You want to create a new table with the results of the dynamic query. I think the simplest approach is to use the create table ... as select ... syntax.

This requires very few changes to your code, and allow you to create the table on the fly based on the results of the query:

SET @sql = CONCAT(
    'CREATE TABLE member AS SELECT user_id, ', 
     @sql, 
    ' FROM wpdg_usermeta GROUP BY user_id'
);

Note that the datatypes of the new table are inferred from the query's metadata; this might, or might no, to exactly what you want. You can check the documentation for more detaiils.

That is excellent, thanks so much that seems to be doing the job — user2448544, Aug 31 '20 at 16:15

Create an insert to pivot value pair values into a table

1 Answers1