Group Concat Two Values Into One field

Question

Group concat is driving me nuts.

I have 1 table 2 columns

FRUIT    QUANTITY
APPLE    4
ORANGE   6

I'd like to group concat these into one field

orange:4, apples:6

It might help for you to check out this post: https://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — md1630, Dec 19 '19 at 00:08

score 0 · Answer 1 · answered Dec 18 '19 at 23:16

0

You can concat() both columns for each row, then group_concat() the results over all rows:

select group_concat(concat(fruit, ':', qty) separator ', ') res from mytable

You might want to consider adding an order by to group_concat() in order to get predictable, consistent results.

answered Dec 18 '19 at 23:16

GMB

score 0 · Answer 2 · answered Dec 18 '19 at 23:27

You can list multiple columns and other values in the GROUP_CONCAT() call, and it will concatenate them together.

SELECT GROUP_CONCAT(fruit, ':', qty SEPARATOR ', ') AS fruits
FROM yourTable

If you have lots of columns that you're joining like this, you could use CONCAT_WS() to concatenate them all together with the same delimiter. See GROUP_CONCAT multiple columns as an array or exolodable string

2 Answers2