MySQL raarrange results from query

Question

This is my table with example data:

|  attr_key  |  attr_value  |  element_id  |<br>
|    color   |    black     |     1        |<br>
|    color   |    red       |     2        |<br>
|    color   |    green     |     3        |<br>
|    power   |    82        |     1        |<br>
|    power   |    150       |     2        |<br>
|    power   |    240       |     3        |<br>
| and so on ...

How can I query this table to get a result like this:

|  color  |  power  |  element_id  |<br>
|  black  |  82     |    1         |<br>
|  red    |  150    |    2         |<br>
|  green  |   240   |    3         |

GROUP BY does not work because it only takes the first attribute for each element_id and the others get lost.

Do I have to create a view?

Would it be a better solution to put the attributes directly into my elements-table, e.g. in a JSON-field? These attributes are very variable, so there can be a lot of attributes for each element (way more than 20) I am not sure which solution will perform better when I have to query and check for specific attributes (all or only a few)..

Later I want to JOIN another table (elements) using the element_id.

Just a note: group by does not take the first element. It groups the results and you’re supposed to use aggregates etc to define what you want out of the grouped data. MySQL allows silly things with group by and returns random results if not done properly but do learn the real functionality and don’t assume things based on what results you happen to see. — Sami Kuhmonen, Jul 02 '18 at 04:29
You should redesign your table to be stored like the second graphic at all times. There is really no need to store the data as you have it laid out in the first table. — Bleach, Jul 02 '18 at 04:38
@Bleach are you serious? This would mean a new colum would have to be added every time someone created a new attribute key. — Joakim Danielson, Jul 02 '18 at 05:02
This transformation is called pivoting and has been asked and answered here on SO sooo many times. The linked duplicate topic demonstrates how to do this transformation in mysq. However, pls note that it may be more efficient to accomplish this in application code as opposed to mysql. — Shadow, Jul 02 '18 at 05:14
@Shadow correct me if I'm wrong, but pivot tables are used to split n-to-m- relationships. In my case it is a 1-n relationship. Each element has specific number of attributes but each attribute belongs to exact one element. — Brotzka, Jul 02 '18 at 05:34
I'm correcting you :) apart from a couple of ORMs, nobody calls junction or association tables as pivot tables (see https://en.m.wikipedia.org/wiki/Associative_entity) You can also check out the pivot tag's description. — Shadow, Jul 02 '18 at 05:49

score 0 · Answer 1 · answered Jul 02 '18 at 05:08

This will do what you need (replace all instances of 'test_table' with your table name):

SELECT
    sub1.`color` AS `Color`,
    sub2.`power` AS `Power`,
    sub1.`element_id` AS `Element ID`
FROM
    (SELECT element_id, attr_value AS `color` FROM test_table WHERE attr_key = 'color') AS sub1
INNER JOIN
    (SELECT  element_id, attr_value AS `power` FROM test_table WHERE attr_key = 'power') AS sub2
    ON
    sub1.element_id = sub2.element_id

Code I used to set up test:

DROP TABLE IF EXISTS test_table;
CREATE TABLE IF NOT EXISTS test_table(
    element_id int,
    attr_key varchar(40),
    attr_value varchar(40)
);

INSERT INTO 
    test_table 
        (attr_key, attr_value, element_id)
    VALUES
        ('color', 'black', 1),
        ('color', 'red', 2),
        ('color', 'green', 3),
        ('power', '82', 1),
        ('power', '150', 2),
        ('power', '240', 3);

MySQL raarrange results from query

1 Answers1