将MySQL结果集从行转换为列 [英] Converting MySQL Resultset from Rows to Columns

问题描述

我从这样的选择中得到了输出:

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

有什么建议吗?

推荐答案

Mmmm ... EAV.避免使用EAV(entity-attribute_value)的许多原因之一是，它们更难以报告和查询.但是，如果您提前知道了所需的属性，则可以执行以下操作:

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

现在，MySQL确实具有GROUP_CONCAT，如果允许，您可以将同一属性的多个值连接到一个列表中(例如，如果一个实体可以具有多个Make属性).

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

这篇关于将MySQL结果集从行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！