MySQL pivot table query with dynamic columns

Question

I'm using the following tables for storing product data:

mysql> SELECT * FROM product; +---------------+---------------+--------+ | id | name     | description   | stock  | +---------------+---------------+--------+ |  1 | product1 | first product |    5   |  |  2 | product2 | second product|    5   |  +---------------+---------------+--------+  mysql> SELECT * FROM product_additional; +-----------------+------------+ | id | fieldname  | fieldvalue | +-----------------+------------+ |  1 | size       | S          | |  1 | height     | 103        | |  2 | size       | L          | |  2 | height     | 13         | |  2 | color      | black      | +-----------------+------------+ 

Using the following query to select the records from both tables

mysql> SELECT      p.id     , p.name     , p.description     ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`     ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`     ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color` FROM product p LEFT JOIN product_additional AS pa ON p.id = pa.id GROUP BY p.id +---------------+---------------+--------+---------+--------+ | id | name     | description   | size   | height  | color  | +---------------+---------------+--------+---------+--------+ |  1 | product1 | first product | S      | 103     | null   | |  2 | product2 | second product| L      | 13      | black  | +---------------+---------------+--------+---------+--------+ 

And everything is working correctly :)

Because i fill the 'additional' table dynamically it would be nice, if the query would also be dynamic. In that way i dont have to change the query everytime i put in a new fieldname and fieldvalue.

Answer 1

The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them:

Dynamic pivot tables (transform rows to columns)

Your code would look like this:

SET @sql = NULL; SELECT   GROUP_CONCAT(DISTINCT     CONCAT(       'MAX(IF(pa.fieldname = ''',       fieldname,       ''', pa.fieldvalue, NULL)) AS ',       fieldname     )   ) INTO @sql FROM product_additional;  SET @sql = CONCAT('SELECT p.id                     , p.name                     , p.description, ', @sql, '                     FROM product p                    LEFT JOIN product_additional AS pa                      ON p.id = pa.id                    GROUP BY p.id, p.name, p.description');  PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

See Demo

NOTE: GROUP_CONCAT function has a limit of 1024 characters. See parameter group_concat_max_len