MySQL pivot table query with dynamic columns
Solution 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
Solution 2
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;
Solution 3
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 ;
fr0sty
Updated on October 01, 2022Comments
-
fr0sty over 1 year
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.
-
fr0sty almost 12 yearsThanks 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.
-
Taryn almost 12 years@fr0sty you will have to test it in PHP. I am not sure exactly how you would proceed in that code.
-
Firegarden over 9 yearsI 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.
-
OnethingSimple almost 9 yearsThis 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!
-
Dhenn almost 8 yearsI'm receiving an error in running this in mySQL workbench. How to get this run?
-
Curtis almost 8 yearswell it works for me. It would depend on what error you are getting
-
Metafaniel about 7 yearsFINALLY, a real answer! Thanks, indeed I was having trouble with
GROUP_CONCAT
and lot of fields. Thanks a lot!! -
Miguel about 6 yearsBy 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;
-
Juan Carlos Oropeza almost 5 yearsTaryn SQL fiddle isn't working. I convert it to rextester I could edit it and replace the link but want give you the chance in case you rather fix the SQL fiddle
-
Taryn almost 5 years@JuanCarlosOropeza Thanks for letting me know, I replaced the link with a db<>fiddle.
-
Adam over 4 years@Taryn are you able to help me? stackoverflow.com/questions/58478168/…
-
Grumpy Civet about 3 years@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 about 3 years@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?
-
Curtis about 3 years@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 db-fiddle.com/f/vysYCm6TKznUPBopV9WpjF/0