A query to extract products and their features from prestashop, with features in columns

extract-products-from-prestashop.sql

select
    subquery.id_product,
    subquery.reference,
    subquery.product_name,
    Max(CASE WHEN subquery.feature_name = 'Feature 1' THEN subquery.feature_value END) AS `Feature 1`,
    Max(CASE WHEN subquery.feature_name = 'Feature 2' THEN subquery.feature_value END) AS `Feature 2`,
    Max(CASE WHEN subquery.feature_name = 'Feature 3' THEN subquery.feature_value END) AS `Feature 3`
from (
	select
		fp.id_product as id_product, 
		p.reference as reference, 
		pl.name as product_name, 
		fl.name as feature_name, 
		fvl.value as feature_value
	from
		ps_feature_product fp
		left join ps_feature_lang fl on (fl.id_feature = fp.id_feature)
		left join ps_feature_value fv on (fv.id_feature_value = fp.id_feature_value)
		left join ps_feature_value_lang fvl on (fvl.id_feature_value = fv.id_feature_value and fvl.id_lang = 1)
		left join ps_product p on (fp.id_product = p.id_product)
		left join ps_product_lang pl on (pl.id_product = p.id_product and pl.id_lang = 1 and pl.id_shop = 1)
	where fl.id_lang = 1
	order by fp.id_product asc
) as subquery
group by subquery.reference
order by subquery.id_product asc

In the subquery it extracts products and features the usual way, with 1 row per feature. Then on the main select, the CASE statements in fact move features in columns. Add as much CASE statements as you want. Works for known columns.

In this gist the query extracts only one language.