I have output from a select like this:
04:47:37> select * from attributes left outer join trailer_attributes on attributes.id = trailer_attributes.attribute_id;
+----+--------------+----------+-----------+------------+--------------+-----------------+
| id | name | datatype | list_page | trailer_id | attribute_id | attribute_value |
+----+--------------+----------+-----------+------------+--------------+-----------------+
| 1 | Make | text | 1 | 1 | 1 | Apple |
| 1 | Make | text | 1 | 2 | 1 | sdfg |
| 2 | Year | number | 1 | 1 | 2 | 2009 |
| 2 | Year | number | 1 | 2 | 2 | sdfg |
| 3 | Type | text | 0 | 1 | 3 | iPhone |
| 3 | Type | text | 0 | 2 | 3 | sdfg |
| 4 | Axles | text | 0 | 1 | 4 | asdf |
| 4 | Axles | text | 0 | 2 | 4 | sdfg |
| 7 | Size | text | 0 | 1 | 7 | asd1 |
| 7 | Size | text | 0 | 2 | 7 | sdfg |
| 8 | Frame | text | 0 | 1 | 8 | |
| 8 | Frame | text | 0 | 2 | 8 | sdfg |
| 9 | Height | text | 0 | 1 | 9 | |
| 9 | Height | text | 0 | 2 | 9 | sdfg |
| 10 | Dollies | text | 0 | 1 | 10 | |
| 10 | Dollies | text | 0 | 2 | 10 | sdfg |
| 11 | Tires/Wheels | text | 0 | 1 | 11 | |
| 11 | Tires/Wheels | text | 0 | 2 | 11 | sdfg |
| 12 | Condition | text | 1 | 1 | 12 | New |
| 12 | Condition | text | 1 | 2 | 12 | sdfg |
| 13 | Title | text | 0 | 1 | 13 | |
| 13 | Title | text | 0 | 2 | 13 | sdfg |
+----+--------------+----------+-----------+------------+--------------+-----------------+
I want to convert it to something more along the lines of:
id, Make, Year, Type, Axles, Size, Frame (etc)
1, Apple, 2009, iPhone, .....
2, sdfg, sdfg, sdfg, .....
Any suggestions?
解决方案
Mmmm...EAVs. One of the many reasons to avoid EAVs (entity-attribute_value) is that they are harder to report and query against. However, if the attributes you want are known ahead of time, you can do something like:
Select id
, Min( Case When name = 'Make' Then attribute_value End ) As Make
, Min( Case When name = 'Year' Then attribute_value End ) As Year
, Min( Case When name = 'Type' Then attribute_value End ) As Type
, Min( Case When name = 'Axles' Then attribute_value End ) As Axles
, Min( Case When name = 'Size' Then attribute_value End ) As Size
, Min( Case When name = 'Frame' Then attribute_value End ) As Frame
, ...
From attributes
Where name In('Make','Year','Type','Axles','Size','Frame',....)
Group By id
Now, MySQL, does have a GROUP_CONCAT which will let you concatenate multiple values for the same attribute into a list if you allow that (e.g. if an entity can have multiple Make attributes).