By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table product
(
id int,
name varchar(10),
description varchar(50),
stock int
);
insert into product values
(1, 'product1', 'first product', 5),
(2, 'product2', 'second product', 5);
create table product_additional
(
id int,
fieldname varchar(10),
fieldvalue varchar(10)
);
insert into product_additional values
(1, 'size', 'S'),
(1, 'height', '103'),
(2, 'size', 'L'),
(2, 'height', '13'),
(2, 'color', 'black');
Records: 2 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
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;
Statement prepared
| id | name | description | size | height | color |
|---|---|---|---|---|---|
| 1 | product1 | first product | S | 103 | null |
| 2 | product2 | second product | L | 13 | black |