I am using the following table to store 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 | ------------------ ------------
Use the following query to select 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 | --------------- --------------- -------- ---------- --- -----
Everything works fine :)
Since I am populating the "additional" table dynamically, it would be nice if the query was also dynamic. This way I don't have to change the query every time I enter a new field name and field value.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
I have a slightly different way of accomplishing this than the accepted answer. This approach avoids using GROUP_CONCAT which has a 1024 character limit by default and won't 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 to view the demo
In MySQL, the only way to do this dynamically is to use prepared statements. Here is a good article about them:
Dynamic pivot table (convert rows to columns)
Your code will 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;ViewDemo
Note: The character limit of the GROUP_CONCAT function is 1024 characters. See parameter group_concat_max_len