SQL Query to retrieve product properties

SQL Query to retrieve product properties.sql

SELECT  spree_products.id AS product_id,
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='THC' ) THEN r.value ELSE NULL END) AS "THC",
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='CBD' ) THEN r.value ELSE NULL END) AS "CBD",
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='Material' ) THEN r.value ELSE NULL END) AS "Material",
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='Ingredients' ) THEN r.value ELSE NULL END) AS "Ingredients",
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='Serving Per Container' ) THEN r.value ELSE NULL END) AS "Serving Per Container",
       MAX(CASE WHEN r.property_id = (select "spree_properties"."id" from "spree_properties" where "spree_properties"."name"='Weight' ) THEN r.value ELSE NULL END) AS "Weight", 	
	spree_products.group_sku_id AS group_id,
        spree_variants.sku as say_hi_sku,
        spree_products.supplier_item_code as supplier_item_code,
        spree_suppliers.name as brand_name,
        spree_products.state_licence as state_licence,
        spree_products.name as product_name,
        spree_products.est_launch_dt as estimation_launch_date,
        spree_products.available_on as sku_live_date,
        spree_products.sku_live_time as	sku_live_time,
        spree_products.discontinue_on as sku_end_date,
        spree_products.sku_end_time as sku_end_time,
        spree_products.sku_on as sku_on_off,
        spree_products.description as manufacture_description,
        spree_products.video_link as video_link,
        spree_products.recommended as say_hi_recommended,
        spree_products.brand_site as brand_website_url,
        spree_prices.amount as retail_price
FROM spree_products
LEFT JOIN spree_variants ON spree_variants.product_id=spree_products.id
LEFT JOIN spree_suppliers ON spree_suppliers.id=spree_products.supplier_id
LEFT JOIN spree_prices ON spree_prices.variant_id=spree_variants.id AND spree_prices.currency = 'USD'
LEFT JOIN "spree_product_properties" r on spree_products.id =r.product_id
GROUP BY spree_products.id, spree_variants.sku, spree_suppliers.name, spree_prices.amount