I’m working with a MySQL database and running into an issue. When I execute this query:
SELECT interests FROM user_interests WHERE user_id = 10;
Current Result:
reading
sports
music
But what I actually need is to get everything in one single row and column like this:
What I Want:
reading, sports, music
The problem is that I’m working with complex queries involving multiple table joins, and this is creating way more rows than I need to handle. I tried looking into CONCAT and CONCAT_WS functions, but they don’t seem to work with multiple result rows from queries.
Is there a way in MySQL to combine multiple row values into one comma-separated field? Any suggestions would be really helpful.
Just wanted to add that you can also customize the separator if comma isn’t what you need. GROUP_CONCAT(interests SEPARATOR ' | ') will use pipes instead of commas, or you can use any other delimiter. I’ve found this particularly useful when the data itself contains commas and you need to avoid confusion during parsing later. Another handy feature is the ORDER BY clause within GROUP_CONCAT - something like GROUP_CONCAT(interests ORDER BY interests ASC) will sort your results alphabetically before concatenating them. This saved me a lot of headache when I needed consistent ordering for comparison purposes in my application logic. The function handles NULL values gracefully too, so you don’t need to worry about those breaking your concatenated string.
The function you’re looking for is GROUP_CONCAT(). It’s specifically designed for this exact scenario where you need to aggregate multiple row values into a single comma-separated string.
Your query should be:
SELECT GROUP_CONCAT(interests) FROM user_interests WHERE user_id = 10;
This will give you exactly what you want: “reading, sports, music” in a single field. I’ve used this countless times in production environments, especially when dealing with tag systems or category listings.
One thing to keep in mind is that GROUP_CONCAT has a default length limit (usually 1024 characters), so if you’re dealing with potentially large result sets, you might want to increase the group_concat_max_len system variable. Also works great in complex JOIN scenarios since it operates at the aggregation level.