Is it possible to aggregate rows like this in MySQL?

Question

With the following table of car models,

+-------+----+-------+
| model | id | total |
+-------+----+-------+
| civic |  1 |   27  |
| civic |  3 |   11  |
| crv   |  3 |    5  |
+-------+----+-------+

would it be possible to query in order to have this result?

+-------+-----+-----+-----+
| model | id1 | id2 | id3 |
+-------+-----+-----+-----+
| civic |  27 |   0 |  11 |
| crv   |   0 |   0 |   5 |
+-------+-----+-----+-----+

The answer is YES. Add 3 case's to your select list, one for each id. — jarlh, Sep 09 '15 at 14:01
You're looking for a **pivot table**. This similar question: [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) would provide a good starting point. — HPierce, Sep 09 '15 at 14:06

score 2 · Accepted Answer · answered Sep 09 '15 at 14:05

This is a pivot query. One way to handle this in MySQL is conditional aggregation:

select model,
       max(case when id = 1 then total else 0 end) as id1,
       max(case when id = 2 then total else 0 end) as id2,
       max(case when id = 3 then total else 0 end) as id3
from carmodels t
group by model;

Is it possible to aggregate rows like this in MySQL?

1 Answers1