MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE product
    (`id` int)
INSERT INTO product
    (`id`)
VALUES
    (1)
CREATE TABLE variant
    (`id` int, `fk_product_id` int)
INSERT INTO variant
    (`id`, `fk_product_id`)
VALUES
    (1, 1),
    (2, 1),
    (3, 1)

Records: 3  Duplicates: 0  Warnings: 0


CREATE TABLE attribute
    (`fk_variant_id` int, `property` varchar(5), `value` varchar(5))
INSERT INTO attribute
    (`fk_variant_id`, `property`, `value`)
VALUES
    (1, 'color', 'red'),
    (1, 'size', 'S'),
    (2, 'color', 'green'),
    (2, 'size', 'S'),
    (3, 'color', 'red'),
    (3, 'size', 'M')

Records: 6  Duplicates: 0  Warnings: 0


SELECT p.id AS product, c.value AS color, GROUP_CONCAT(s.value) AS sizes
FROM product p
JOIN variant v ON v.fk_product_id = p.id
LEFT JOIN attribute c ON c.fk_variant_id = v.id AND c.property = 'color'
LEFT JOIN attribute s ON s.fk_variant_id = v.id AND s.property = 'size'
GROUP BY product, color


product
color
sizes


1
green
S

1
red
S,M