Group rows by Id and concatenate month & order

Currently I have an orders table that is formatted with a row per month:

id	order_month	order_count	order_sum
111	2021-07	5	50
111	2021-08	10	50
111	2021-09	1	100
222	2021-07	8	80
222	2021-08	2	50
222	2021-09	1	80

Is there a way to format the SQL query so that the ouput has 1 row per id, and the other values are added as columns? E.g. something like:

id	2021-07_order_count	2021-07_order_sum	2021-08_order_count	2021-08_order_sum	2021-09_order_count	2021-09_order_sum
111	5	50	10	50	1	100
222	8	80	2	50	1	80

I think I am close with the following query:

SELECT 
    merchant_id,
    (CASE WHEN order_month = '2021-07' THEN order_count ELSE 0 END) as '2021-07-orderCount',
    (CASE WHEN order_month = '2021-07' THEN order_sum ELSE 0 END) as '2021-07-orderSum',
    (CASE WHEN order_month = '2021-08' THEN order_count ELSE 0 END) as '2021-08-orderCount',
    (CASE WHEN order_month = '2021-08' THEN order_sum ELSE 0 END) as '2021-08-orderSum',
    (CASE WHEN order_month = '2021-09' THEN order_count ELSE 0 END) as '2021-09-orderCount',
    (CASE WHEN order_month = '2021-09' THEN order_sum ELSE 0 END) as '2021-09-orderSum'
FROM orders
ORDER BY id

It is creating a separate column and putting the correct values in each column.

However when I try and group by Id it then only shows the first result:

Thank you.

CodePudding user response：

You need conditional aggregation:

SELECT id,
       MAX(CASE WHEN order_month = '2021-07' THEN order_count ELSE 0 END) `2021-07-orderCount`,
       MAX(CASE WHEN order_month = '2021-07' THEN order_sum ELSE 0 END) `2021-07-orderSum`,
       MAX(CASE WHEN order_month = '2021-08' THEN order_count ELSE 0 END) `2021-08-orderCount`,
       MAX(CASE WHEN order_month = '2021-08' THEN order_sum ELSE 0 END) `2021-08-orderSum`,
       MAX(CASE WHEN order_month = '2021-09' THEN order_count ELSE 0 END) `2021-09-orderCount`,
       MAX(CASE WHEN order_month = '2021-09' THEN order_sum ELSE 0 END) `2021-09-orderSum`
FROM orders
GROUP  BY id
ORDER BY id;

See the demo.