MySQL Consolidating rows in a table belonging to the same id based on condition in one of the columns in the same table

Question

I have the following table which collects accounts details for clients. 1 client is allowed to have multiple accounts of different products i.e.

id  acc_balances    Product_type
1   2000                 4
1   900                  5
1   1100                 6
2   580                  6
2   70                   5
2   10                   4

The following is the desired outcome:

id  Product_type_4  Product_type_5  Product_type_6
1   2000                 900             1100
2   10                   70              580

@arilia thanks a lot for directing me to this resource..... exactly what I was Looking for. This basically is a pivot table. A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78 I advise reading this post and adapt this solution to your needs. — Kevin Kimani, May 05 '16 at 08:25

score 0 · Answer 1 · answered May 05 '16 at 07:14

Try this

Fiddle Demo

select id,sum(case when product_type = 4
then acc_balances end) as Product_type_4,
sum(case when product_type = 5
then acc_balances end) as Product_type_5,
sum(case when product_type = 6
then acc_balances end) as Product_type_6
from accounts
group by id

MySQL Consolidating rows in a table belonging to the same id based on condition in one of the columns in the same table

1 Answers1