Mysql - How to Join Tables for Custom Field Headers and Data within 1 record

Products Table:

id   product_name
-----------------
20   Toy Car开发者_如何学运维

Product_attr Table:

id  product_id  label  value
-----------------------------
1   20          Price  20.00
2   20          Color  Red

Need sql statement to return one line containing values like so with headers.

id,  product_name,  price,  color
----------------------------------
20   Toy Car        20.00    Red

This is what you are looking for:

SELECT 
  Products.id, 
  Products.product_name,
  Max(case label when 'Price' then product_attr.value else null end) as price,
  Max(case label when 'Color' then product_attr.value else null end) as color
FROM Products 
  LEFT JOIN product_attr 
    ON Products.is = product_attr.product_id
GROUP BY Post.headline, Post.date

However, I should write some considerations on your design:

• What happens if a product has more than one record d=for, say, color? (same applies to price)
• LEFT JOIN is to make sure products always come. But, can a product not have a color? or price?
• You are trying to have generic attributes, but you are losing the typing system, mixing strings and numbers (value field in product_attr). This is confusing and never ends up well...
• All in all: a product has a color? Declare a color field in products! A product has a price? Declare a price field in products!