在Mysql相同的Id不同的列值显示并排使用Mysql查询

Question

实际上，我得到的结果使用MySQL查询

id    title product_id  name    value
974   test  337         Color   BLUE
974   test  337         Size    XL

我想像下面这样使用MySQL查询

id    title product_id  Color  Size
974   test  337         BLUE   XL 

是否可以写一个查询合并两行作为单行使用id?

我的查询如下

SELECT DISTINCT i.id, i.title, i.product_id, a.name, av.value from inventories as i 
left JOIN products as p on i.product_id = p.id 
left JOIN attribute_inventory as ai on ai.inventory_id = i.id 
left JOIN attribute_values as av on av.id = ai.attribute_value_id
left JOIN attributes as a on a.id = ai.attribute_id
WHERE i.id = 974

Answer 1

我的问题有答案了

SELECT DISTINCT i.id, i.title,
max(case when (a.name='Color') then av.value end) as 'Color',
max(case when (a.name='Size') then av.value end) as 'Size'
from inventories as i 
left JOIN attribute_inventory as ai on ai.inventory_id = i.id 
left JOIN attribute_values as av on av.id = ai.attribute_value_id
left JOIN attributes as a on a.id = ai.attribute_id
WHERE i.id = 974
group by i.id