pivoting mysql table doesnt work

Question

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

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

prodName propName propValue

I want to query so it will be represented 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 am getting all NULLs besides prodName

For example:

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

above query results in:

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

Answer 1

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';

Answer 2

You need to wrap the CASE into 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;