Mysql table expansion not working

Question

I already read artfulsoftware and numerous topics here, but still can not understand why I can not make it work.

I have a very simple table (productProperties pp) with three columns:

prodName propName propValue

I want to request that it be presented as (for example)

prodName Color  Weight Adhesive
nail1    black  1lbs   3m 
nail2    silver 0.5lbs 3m

What I tried:

select 
    pp.prodName,
     CASE pp.propName
        WHEN 'Color' THEN pp.propValue
        ELSE NULL
    END as 'Color',

   CASE pp.propName
        WHEN 'Weight' THEN pp.propValue
        ELSE NULL
    END as 'Weight',

   CASE pp.propName
        WHEN 'Adhesive' THEN pp.propValue
        ELSE NULL
    END as 'Adhesive'      
from
    ProductsProperties as pp    
group by pp.prodName;

I get all NULL except prodName

For instance:

prodName propName   propValue
nail1    Color      black
nail1    Adhesive   3M
nail2    Weight     1lbs
nail3    Color      black
nail3    Adhesive   3M
nail5    Weight     1lbs

above query results:

 prodName, Color,   Weight, Adhesive
 nail1,    black,   NULL,   NULL
 nail2,    NULL,    1lbs,   NULL
 nail3,    black,   NULL,   NULL
 nail5,    NULL,    1lbs,   NULL

Answer 1

You need to wrap CASE in GROUP_CONCAT():

SELECT
    pp.prodName,
    GROUP_CONCAT(DISTINCT CASE pp.propName
        WHEN 'Color' THEN pp.propValue
        ELSE NULL
    END) AS'Color',

    GROUP_CONCAT(DISTINCT CASE pp.propName
        WHEN 'Weight' THEN pp.propValue
        ELSE NULL
    END) AS 'Weight',

    GROUP_CONCAT(DISTINCT CASE pp.propName
        WHEN 'Adhesive' THEN pp.propValue
        ELSE NULL
    END) AS 'Adhesive'      
FROM
    ProductsProperties AS pp    
GROUP BY pp.prodName;

Answer 2

Solution using join:

select distinct pp1.prodName, 
       pp2.propValue as 'Color', 
       pp3.propValue as 'Adhesive', 
       pp4.propValue as 'Weight' 
from ProductsProperties pp1 
  left join ProductsProperties pp2 on pp1.prodName = pp2.prodName and pp2.propName = 'Color'
  left join ProductsProperties pp3 on pp1.prodName = pp3.prodName and pp3.propName = 'Adhesive'
  left join ProductsProperties pp4 on pp1.prodName = pp4.prodName and pp4.propName = 'Weight';