MYSQL – Set data into one column rather then two

Issue

Here is a mysql query that results two rows
As you can see the second row is coming from this sub query in below query

LEFT JOIN catalog_product_entity_decimal AS cped ON cped.row_id = simple.entity_id
    AND `attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute`
    WHERE `attribute_code` IN ('price','special_price')) AND cped.store_id IN (13,0)

The row has price = price & other has special_price = price, i want them into single row
is that possible ?

SELECT parent.entity_id AS parent_id,
    simple.entity_id AS simple_id,
    simple.sku AS simple_sku,
    cpp.rule_price AS rule_price,
    cped.value AS price,
    cpev.value AS simple_name,
    parent.type_id AS type_id,
    wi.store_id AS wishlist_store_id
FROM catalog_product_entity AS parent
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id
LEFT JOIN catalogrule_product_price AS cpp ON cpp.product_id = simple.entity_id AND
    cpp.website_id = (SELECT `website_id` FROM `store` WHERE `store_id` = '13')
LEFT JOIN catalog_product_entity_decimal AS cped ON cped.row_id = simple.entity_id
    AND `attribute_id` IN (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('price','special_price')) 
    AND cped.store_id IN (13,0)
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpev.row_id = simple.entity_id
    AND cpev.store_id IN (13,0) AND cpev.attribute_id = (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('name') 
        order by `attribute_id` DESC 
        LIMIT 1)
LEFT JOIN wishlist_item AS wi ON parent.row_id = wi.product_id AND `wishlist_id` = '1'
WHERE parent.entity_id = '9244' 
order by cped.store_id DESC 
LIMIT 2

Solution

This is a pivot, so use the usual idiom for pivoting in MySQL: aggregate a conditional with each value.

SELECT parent.entity_id AS parent_id,
    simple.entity_id AS simple_id,
    simple.sku AS simple_sku,
    cpp.rule_price AS rule_price,
    MAX(IF(cped.attribute_code = 'price', cped.value, NULL)) AS price,
    MAX(IF(cped.attribute_code = 'special_price', cped.value, NULL)) AS special_price,
    cpev.value AS simple_name,
    parent.type_id AS type_id,
    wi.store_id AS wishlist_store_id
FROM catalog_product_entity AS parent
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id
LEFT JOIN catalogrule_product_price AS cpp ON cpp.product_id = simple.entity_id AND
    cpp.website_id = (SELECT `website_id` FROM `store` WHERE `store_id` = '13')
LEFT JOIN (SELECT cped.store_id, cped.row_id, cped.value, ea.attribute_code
           FROM catalog_product_entity_decimal AS cped
           JOIN eav_attribute AS ea ON ea.attribute_id = cped.attribute_id
           WHERE ea.attribute_code IN ('price','special_price')
           AND cped.store_id IN (13,0)) AS cped ON cped.row_id = simple.entity_id
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpev.row_id = simple.entity_id
    AND cpev.store_id IN (13,0) AND cpev.attribute_id = (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('name') 
        order by `attribute_id` DESC 
        LIMIT 1)
LEFT JOIN wishlist_item AS wi ON parent.row_id = wi.product_id AND `wishlist_id` = '1'
WHERE parent.entity_id = '9244' 
GROUP BY parent_id, simple_id, simple_sku, rule_price, simple_name, type_id, wishlist_store_id
ORDER BY cped.store_id DESC
LIMIT 1

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0