Ce script SQL permet de faire un listing des produits prestashop avec leurs caractéristiques. Utilisation d'un pivot pour avoir une ligne par produit et en colonne ses caractéristiques.

prestashop_listing-product_with_feature.sql

SET group_concat_max_len = 18446744073709551615;
SET @COLUMNS = NULL;

SELECT GROUP_CONCAT(
		CONCAT(
        'GROUP_CONCAT(IF(' , SR1A.id_feature , ' = B.id_feature, D.value, NULL)) AS "',
        SR1B.name ,
        '"'
		)
) INTO @COLUMNS
FROM ps_feature SR1A
	INNER JOIN ps_feature_lang SR1B ON SR1B.id_feature = SR1A.id_feature
WHERE SR1B.id_lang=1;

SET @SQL = CONCAT(
        'SELECT 
        	A.reference,
        	A.id_product,
        	A.price,
        	A.active,
        	PL.name,
        	M.name,
        	A.width,
        	A.height,
        	A.depth,
        	A.weight,       	
	   		',@COLUMNS,'
		 FROM ps_product A
	INNER JOIN ps_feature_product B ON B.id_product = A.id_product
	INNER JOIN ps_feature_value C ON C.id_feature_value = B.id_feature_value
	INNER JOIN ps_feature_value_lang D ON D.id_feature_value = C.id_feature_value
	INNER JOIN ps_product_lang PL ON PL.id_product = A.id_product
	INNER JOIN ps_manufacturer M ON M.id_manufacturer = A.id_manufacturer
WHERE D.id_lang = 1 AND PL.id_lang = 1 GROUP BY A.reference  
'       
);

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;