mysql multiple rows in one result row one table

Question

I have a table which looks as following:

    + ------- + -----------+ ------------ + --------- + ---------- +
    | Info_Id | category   | EAN          | Info_Type | Info_Value |
    + ------- + -----------+ ------------ + --------- + ---------- +
    | 1       | 1          | 0123456789   | brand     | brand1     |
    | 2       | 1          | 0123456789   | type      | type1      |
    | 3       | 1          | 0123456789   | price     | 0.00       |
    | 4       | 2          | 9876543210   | brand     | brand6     |
    | 5       | 2          | 9876543210   | type      | type3      |
    | 6       | 2          | 9876543210   | price     | 15.00      |
    | 7       | 2          | 6548214656   | brand     | brand34    |
    | 8       | 2          | 6548214656   | type      | type1      |
    | 9       | 2          | 6548214656   | price     | 99.00      |
    | 10      | 3          | 245511411241 | brand     | brand324   |
    | 11      | 3          | 245511411241 | type      | type1      |
    | 12      | 3          | 245511411241 | price     | 98.00      |
    + ------- + -----------+ ------------ + --------- + ---------- +

Now, I am looking for an query that creates the following output:

    + ------------ + ---------- + --------- + ---------- + ----------+
    | EAN          | category   | brand     | type       | price     |
    + ------------ + ---------- + --------- + ---------- + ----------+
    | 0123456789   | 1          | brand1    | type1      | 0.00      |
    | 9876543210   | 2          | brand6    | type3      | 15.00     |
    | 6548214656   | 2          | brand34   | type1      | 99.00     |
    | etc.         | etc.       | etc.      | etc.       | etc.      |
    + ------------ + ---------- + --------- + ---------- + --------- +

I have the following, but that does not seems to work:

    SELECT ean,
           GROUP_CONCAT(Info_Type)    AS Info_Type,
           GROUP_CONCAT(Info_Value) AS Info_Value
    FROM tablename WHERE category=2
    GROUP BY EAN

However, this provides me something like:

    + ------------ + ---------------- + ------------------- + 
    | EAN          | Info_Type        | Info_Value          | 
    + ------------ + ---------------- + ------------------- +
    | 0123456789   | brand,type,price | brand1,type1,0.00   |
    | 9876543210   | brand,type,price | brand6,type3,15.00  |
    | 6548214656   | brand,type,price | brand34,type1,99.00 |
    | etc.         | etc.             | etc.                |
    + ------------ + ---------------- + ------------------- +

How do I do this the right way?

Gordon Linoff · Accepted Answer · 2017-03-25 13:39:16Z

1

I think you just want conditional aggregation:

select ean, category,
       max(case when info_type = 'brand' then info_value end) as brand,
       max(case when info_type = 'type' then info_value end) as type,
       max(case when info_type = 'price' then info_value end) as price
from t
where category = 2
group by ean, category;

answered Mar 25, 2017 at 13:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bernd Buffen Over a year ago

why you group by category if you only have category 2 in the where

Gordon Linoff Over a year ago

@BerndBuffen . . . (1) I always group by all unaggregated columns; (2) The OP's output has categories other than "2", so I'm not sure if s/he intends the where clause.

mh3982 Over a year ago

This seems to work, but is a bit slow. Table has indexes, how can I improve this query even more? (1M rows)

Gordon Linoff Over a year ago

@mh3982 . . . An index on t(category, ean, info_type, info_value) might help.

mh3982 Over a year ago

@GordonLinoff an index at all of them together? Or individual indexes for each column?

|

Bernd Buffen · Accepted Answer · 2017-03-25 13:33:02Z

0

You must modify each GROUP_CONCAT like this:

SELECT ean,
  GROUP_CONCAT(IF(Info_Type = 'type' ,Info_Type,''))    AS 'type',
  GROUP_CONCAT(IF(Info_Type = 'price' ,Info_Type,''))    AS 'price'
FROM tablename WHERE category=2
GROUP BY EAN;

answered Mar 25, 2017 at 13:33

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

mysql multiple rows in one result row one table

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related