I use 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 two 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 is fine :)
Since I'm populating the "additional" table dynamically, it would be great 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)
My approach is slightly different from the accepted answer. This way you avoid using GROUP_CONCAT, which has a default limit of 1024 characters, which 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
The only way to do this dynamically in MySQL is to use prepared statements. Here is a good article about them:
Dynamic PivotTable (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;SeeDemo
Note: The length of the GROUP_CONCAT function is limited to 1024 characters. View parameter group_concat_max_len