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.

possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — RichardTheKiwi, May 03 '13 at 09:00

score 46 · Accepted Answer · edited May 17 '21 at 16:21

46

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

edited May 17 '21 at 16:21

Curtis

2,813
5
26
39

answered Sep 26 '12 at 10:11

Taryn

234,956
54
359
399

Thanks for your quick answer! So in PHP this would mean, that i do 2 queries. One for getting the string of columns and the second one where i concat the string into the full query? Using Zend Framework here. – fr0sty Sep 26 '12 at 12:21
@fr0sty you will have to test it in PHP. I am not sure exactly how you would proceed in that code. – Taryn Sep 26 '12 at 12:22
I just used this example and reapplied it to pull data from wp_posts and wp_postmeta and it was a huge help. Thank you. The only thing I needed to add was the call to SET group_concat_max_len=150000; so it would not give warning. – Firegarden Jan 14 '15 at 08:17
1

This was a great help. I applied this method to similiar problem and rather than concat a SELECT statement I created an event scheduled every couple hours to rebuild a view that pivots n amount of rows from one table into n columns on the other. It's a big help because before I was rebuilding the query using PHP on every execution of the SELECT. Even though views can't leverage Indexes, I'm thinking filtering performance won't be an issue as the pivoted rows->columns represent types of training employees at a franchise have so the view won't ever break a few thousand rows. THANKS @bluefeet! – OnethingSimple Aug 16 '15 at 16:28
Taryn SQL fiddle isn't working. I convert it to [rextester](https://rextester.com/XPGG41886) I could edit it and replace the link but want give you the chance in case you rather fix the SQL fiddle – Juan Carlos Oropeza Aug 07 '19 at 14:18
1

@JuanCarlosOropeza Thanks for letting me know, I replaced the link with a db<>fiddle. – Taryn Aug 07 '19 at 14:21
@Taryn are you able to help me? https://stackoverflow.com/questions/58478168/mysql-pivot-table-query-with-dynamic-columns-truncating-key-value – Adam Oct 20 '19 at 23:37
@Taryn Thanks for the answer! I tried to run your code - it works for MySQL 5.6 but not MySQL 8.0. Could you please help make it compatible with 8.0? – Grumpy Civet May 17 '21 at 08:18

Curtis · Answer 2 · 2021-05-17T16:54:42.673

9

I have a slightly different way of doing this than the accepted answer. This way you can avoid using GROUP_CONCAT which has a limit of 1024 characters by default and will not work if you have a lot of fields unless you change the limit.

SET @sql = '';
SELECT
    @sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
    SELECT
      DISTINCT
        CONCAT(
         'MAX(IF(pa.fieldname = ''',
          fieldname,
          ''', pa.fieldvalue, NULL)) AS ',
          fieldname
        ) as output
    FROM
        product_additional
) as temp;

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;

Click here for demo

edited May 17 '21 at 16:54

answered Jun 15 '16 at 18:37

Curtis

2,813
5
26
39

I'm receiving an error in running this in mySQL workbench. How to get this run? – Dhenn Aug 04 '16 at 10:06
well it works for me. It would depend on what error you are getting – Curtis Aug 24 '16 at 16:01
FINALLY, a real answer! Thanks, indeed I was having trouble with `GROUP_CONCAT` and lot of fields. Thanks a lot!! – Metafaniel Apr 24 '17 at 17:46
1

By the way the limit of group_concat can be defined, SET SESSION group_concat_max_len = 1000000; Above is set for SESSION SET GLOBAL group_concat_max_len = 1000000; – Miguel Jun 26 '18 at 15:32
@Curtis Thanks for the answer! I tried to run your code - it works for MySQL 5.6 but not MySQL 8.0. Could you please help make it compatible with 8.0? – Grumpy Civet May 17 '21 at 08:34
1

@GrumpyCivet The issue you are having is most likely due to having `ONLY_FULL_GROUP_BY` enabled. Simple solution is to fill in the rest of the columns in the Group By clause of the generated query.... Which I have now done. So it should work. Here is a fiddle of it working https://www.db-fiddle.com/f/vysYCm6TKznUPBopV9WpjF/0 – Curtis May 17 '21 at 16:10

score 2 · Answer 3 · answered Jan 22 '14 at 13:32

Here's stored procedure, which will generate the table based on data from one table and column and data from other table and column.

The function 'sum(if(col = value, 1,0)) as value ' is used. You can choose from different functions like MAX(if()) etc.

delimiter //

  create procedure myPivot(
    in tableA varchar(255),
    in columnA varchar(255),
    in tableB varchar(255),
    in columnB varchar(255)
)
begin
  set @sql = NULL;
    set @sql = CONCAT('select group_concat(distinct concat(
            \'SUM(IF(', 
        columnA, 
        ' = \'\'\',',
        columnA,
        ',\'\'\', 1, 0)) AS \'\'\',',
        columnA, 
            ',\'\'\'\') separator \', \') from ',
        tableA, ' into @sql');
    -- select @sql;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
    -- select @sql;

    SET @sql = CONCAT('SELECT p.', 
        columnB, 
        ', ', 
        @sql, 
        ' FROM ', tableB, ' p GROUP BY p.',
        columnB,'');

    -- select @sql;

    /* */
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    /* */
    DEALLOCATE PREPARE stmt;
end//

delimiter ;

MySQL pivot table query with dynamic columns

3 Answers3

Linked

Related