mysql列变成行展示_将MySQL结果集从行转换为列

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).