CODEWITHSUNDEEP
mysqlsql
Sriya
Sriya

Reputation: 187

mysql select dynamic row values as column names as follow

This is my mysql query 

SELECT research_id,
@availability:=if(product_id=4,value,'') AS availability,
@cooler:=if(product_id=5,value,'') AS Cooler,
@coolerLocation:=if(product_id=9,value,'') AS CoolerLocation
FROM research_product_details rpd
LEFT JOIN products p
ON rpd.product_id = p.id
WHERE product_id = 4 OR product_id = 5 OR product_id = 9
ORDER BY research_id ASC, product_id ASC

and i got this result

enter image description here

And this is not the result what i want, i want it like this,

enter image description here

Upvotes: 1

Views: 157

Answers (1)

PM 77-1
PM 77-1

Reputation: 13334

Use GROUP BY with MAX() to flatten the table

SELECT research_id,
    MAX(if(product_id=4,value, NULL)) AS availability,
    MAX(if(product_id=5,value, NULL)) AS Cooler,
    MAX(if(product_id=9,value, NULL)) AS CoolerLocation
FROM research_product_details rpd
LEFT JOIN products p
ON rpd.product_id = p.id
WHERE product_id = 4 OR product_id = 5 OR product_id = 9
GROUP BY research_id 
ORDER BY research_id ASC, product_id ASC

Upvotes: 2

Related Questions