How can I make value of a record to be title of field on the mysql?

Question

My query like this :

SELECT a.number, a.description, b.attribute_code, b.attribute_value 
FROM items a 
JOIN attr_maps b ON b.number = a.number WHERE a.number = AB123

If the query executed, the result like this :

I want to make the result like this :

How can I do it?

[MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Lukasz Szozda, Sep 08 '18 at 09:23

score 1 · Accepted Answer · answered Sep 08 '18 at 13:35

1

You can use conditional aggregation:

SELECT i.number, i.description,
       MAX(CASE WHEN am.attribute_code = 'brand' then am.attribute_value END) as brand,
       MAX(CASE WHEN am.attribute_code = 'model' then am.attribute_value END) as model,
       MAX(CASE WHEN am.attribute_code = 'category' then am.attribute_value END) as category,
       MAX(CASE WHEN am.attribute_code = 'subcategory' then am.attribute_value END) as subcategory
FROM items i JOIN
     attr_maps am
     ON am.number = i.number
WHERE i.number = AB123
GROUP BY i.number, i.description

answered Sep 08 '18 at 13:35

Gordon Linoff

1,242,037
58
646
786

Great. It works. Thanks a lot – moses toh Sep 09 '18 at 00:16
How can I filter by brand or model or category or subcategory. I try to add `WHERE brand = 'honda'`, there exist error like this : `#1054 - Unknown column 'brand' in 'where clause'` – moses toh Sep 09 '18 at 01:08
1

@SuccessMan . . . You can use a `having` clause . . . `HAVING brand = 'honda'`. – Gordon Linoff Sep 10 '18 at 09:05

How can I make value of a record to be title of field on the mysql?

1 Answers1