A query for extract a list of sku and variants

magento_get_all_super_attributes_from_simple_products.sql

SELECT
-- cpe.entity_id as product_id,
cpe.sku,
cpsal.value  as variant_name,
eaov.value as variant_default,
eaov_it.value as variant_it,
eaov_en.value as variant_en,
eaov_de.value as variant_de,
eaov_fr.value as variant_fr,
eaov_es.value as variant_es

FROM `catalog_product_entity` as cpe

-- search all super attributes configurable
LEFT JOIN `catalog_product_super_link` as cpsl
	ON cpe.entity_id = cpsl.product_id

LEFT JOIN `catalog_product_super_attribute` as cpsa
	ON cpsl.parent_id = cpsa.product_id

LEFT JOIN `catalog_eav_attribute` as cea
	ON cpsa.attribute_id = cea.attribute_id
		AND cea.is_configurable = 1


-- take label in admin (in select as variant_name)
LEFT JOIN `catalog_product_super_attribute_label` as cpsal
	ON cpsa.product_super_attribute_id = cpsal.product_super_attribute_id
		AND cpsal.store_id = 0


-- check if attribute is in product and take option_id (cpei.value)
LEFT JOIN `catalog_product_entity_int` as cpei
	ON cpsa.attribute_id = cpei.attribute_id
		AND cpe.entity_id = cpei.entity_id

-- take value of attribute for product
LEFT JOIN `eav_attribute_option` as eao
	ON cpei.value = eao.option_id

-- take values for Default stores views
LEFT JOIN `eav_attribute_option_value` as eaov
	ON  eao.option_id =  eaov.option_id
		AND eaov.store_id = 0

-- take values for IT
LEFT JOIN `eav_attribute_option_value` as eaov_it
	ON  eao.option_id =  eaov_it.option_id
		AND eaov_it.store_id = 1


-- take values for EN
LEFT JOIN `eav_attribute_option_value` as eaov_en
	ON  eao.option_id =  eaov_en.option_id
		AND eaov_en.store_id = 2

-- take values for DE
LEFT JOIN `eav_attribute_option_value` as eaov_de
	ON  eao.option_id =  eaov_de.option_id
		AND eaov_de.store_id = 4

-- take values for FR
LEFT JOIN `eav_attribute_option_value` as eaov_fr
	ON  eao.option_id =  eaov_fr.option_id
		AND eaov_fr.store_id = 5

-- take values for FR
LEFT JOIN `eav_attribute_option_value` as eaov_es
	ON  eao.option_id =  eaov_es.option_id
		AND eaov_es.store_id = 6

WHERE cpe.type_id = 'simple'