CREATE TABLE `prodotti_attributi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` int(11) DEFAULT NULL,
  `sku` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `attribute_code` varchar(255) DEFAULT NULL,
  `value` text,
  PRIMARY KEY (`id`),
  KEY `idx_entity_id` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1245674 DEFAULT CHARSET=utf8

INSERT INTO prodotti_attributi (entity_id, sku, created_at, updated_at, attribute_code, value)
SELECT
    e.entity_id,
    e.sku,
    e.created_at,
    e.updated_at,
    ea.attribute_code,
    CASE ea.backend_type
        WHEN 'varchar' THEN ev.value
        WHEN 'int' THEN ei.value
        WHEN 'decimal' THEN ed.value
        WHEN 'text' THEN et.value
        WHEN 'datetime' THEN edate.value
        ELSE NULL
    END AS value
FROM catalog_product_entity AS e
JOIN eav_attribute AS ea ON ea.entity_type_id = 4 -- 4 is typically for products in Magento 2
LEFT JOIN catalog_product_entity_varchar AS ev ON ev.attribute_id = ea.attribute_id AND ev.entity_id = e.entity_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ei ON ei.attribute_id = ea.attribute_id AND ei.entity_id = e.entity_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_decimal AS ed ON ed.attribute_id = ea.attribute_id AND ed.entity_id = e.entity_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_text AS et ON et.attribute_id = ea.attribute_id AND et.entity_id = e.entity_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_datetime AS edate ON edate.attribute_id = ea.attribute_id AND edate.entity_id = e.entity_id AND ea.backend_type = 'datetime';

CREATE INDEX idx_entity_id ON prodotti_attributi (entity_id);

SELECT
    cpe.entity_id AS 'Product_ID', 
    cpev.value AS 'Product_Name', 
    GROUP_CONCAT(DISTINCT ccv.value) AS 'Category_Names',
    GROUP_CONCAT(DISTINCT ccp.category_id) AS 'Category_IDs',
    MAX(ur.request_path) AS 'Product_URL'
FROM
    catalog_product_entity AS cpe
JOIN
    catalog_product_entity_varchar AS cpev ON cpe.entity_id = cpev.entity_id AND cpev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
    catalog_category_product AS ccp ON cpe.entity_id = ccp.product_id
LEFT JOIN
    catalog_category_entity_varchar AS ccv ON ccp.category_id = ccv.entity_id AND ccv.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
LEFT JOIN
    url_rewrite AS ur ON ur.entity_id = cpe.entity_id AND ur.entity_type = 'product' AND ur.redirect_type = 0 AND ur.store_id = 1
GROUP BY
    cpe.entity_id, cpev.value;