DKprojektai DKprojektai - 1 month ago 6
MySQL Question

MySQL row into number of columns sum and payment option

I have a table with records such as:

ID | Car_num | Service | Price | Payment
---+---------+---------+-------+-------+-
1 | 001 | shower | 10 | card
2 | 002 | TV | 5 | cash
3 | 001 | TV | 5 | cash


How to write an SQL query to get the following output?

ID |Car_num | shower | TV
---+--------+------------+---
1 | 001 | 10 (card) | 5 (cash)
2 | 002 | | 5 (cash)

Answer

Use conditional aggregation:

SELECT MIN(t.id) as id, 
       t.car_num,
       MAX(CASE WHEN t.service = 'shower' THEN t.price END) as shower,
       MAX(CASE WHEN t.service = 'TV' THEN t.price END) as TV
FROM YourTable t
GROUP BY t.car_num

If you want the columns to actually appear like 10 (card) and not 10 (which is not recommended at all), then change it to this:

MAX(CASE WHEN t.service = 'shower' THEN concat(t.price,'(',t.payment,')') END) as shower,
MAX(CASE WHEN t.service = 'TV' THEN concat(t.price,'(',t.payment,')') END) as TV
Comments