fr0sty fr0sty - 5 months ago 104
MySQL Question

MySQL pivot table query with dynamic columns

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

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');

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

See SQL Fiddle with Demo

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