By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `psfr_product_attribute` (
`id_product_attribute` int UNSIGNED NOT NULL,
`id_product` int UNSIGNED NOT NULL,
`quantity` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `psfr_product_attribute` (`id_product_attribute`, `id_product`, `quantity`) VALUES
(195, 59, 5),
(196, 59, 0),
(197, 59, 5),
(198, 59, 0),
(199, 59, 5),
(200, 59, 5),
(201, 59, 5),
(202, 59, 5);
CREATE TABLE `psfr_product_attribute_combination` (
`id_attribute` int UNSIGNED NOT NULL,
`id_product_attribute` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `psfr_product_attribute_combination` (`id_attribute`, `id_product_attribute`) VALUES
(2, 195),
(10, 195),
(2, 196),
(11, 196),
(3, 197),
(10, 197),
(3, 198),
(11, 198),
(4, 199),
(10, 199),
(4, 200),
(11, 200),
(1, 201),
Records: 8 Duplicates: 0 Warnings: 0
Records: 16 Duplicates: 0 Warnings: 0
Records: 12 Duplicates: 0 Warnings: 0
WITH temp(id, attr) AS
(
SELECT pa.`id_product_attribute`, al.`name`
FROM `psfr_product_attribute` pa
JOIN `psfr_product_attribute_combination` pac ON(pa.`id_product_attribute` = pac.`id_product_attribute`)
JOIN `psfr_attribute_lang` al ON(pac.`id_attribute` = al.`id_attribute`)
WHERE `id_product` = 59 AND al.`id_lang` = 1 AND pa.`quantity` > 0
ORDER BY pa.`id_product_attribute`
)
SELECT id,
MAX(CASE WHEN (attr) IN ('S','M','L','XL') THEN (attr) END )AS size,
MAX(CASE WHEN (attr) NOT IN ('S','M','L','XL') THEN (attr) END) AS colour
FROM temp GROUP BY id ORDER BY id;
| id | size | colour |
|---|---|---|
| 195 | M | Red |
| 197 | L | Red |
| 199 | XL | Red |
| 200 | XL | Black |
| 201 | S | Red |
| 202 | S | Black |