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
Funnily enough, your code in the question helped me solve my own problem — Elex Promise, Jul 26 '22 at 07:34
A Stored Proc to do the task: http://mysql.rjweb.org/doc.php/pivot — Rick James, Aug 21 '22 at 13:24

Taryn · Accepted Answer · 2023-02-27T14:02:22.890

47

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 Feb 27 '23 at 14:02

answered Sep 26 '12 at 10:11

Taryn

242,637
56
362
405

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
First link from this answer (to article about pivot tables) is currently on list of uBlock filters marked as badware risks i.e. sites documented to put users at risk of installing adware/crapware. – Marek J Feb 27 '23 at 08:29

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

10

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

3,170
7
28
44

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
1

Clever solution! I've modified it slightly for my uses. If you don't want more than one result returned, limit the first select statement to the last row, and dump the result into a variable. Example here: https://www.db-fiddle.com/f/cJ2RXHBDQ4iybur59DsHB2/1 – scullytr Nov 08 '22 at 23:23

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