mySQL multiple columns in group concat

Question

I'm trying to select multiple columns from multiple tables into one single column as a string (since this is part of a larger query)

Basically what I'm trying to do is make sub-groups of an ID and a Name, The only problem however is that I can't find a way to split these after they already have had a separator assigned to the parent group.

My code:

SELECT 
    GROUP_CONCAT(`mytable1`.`id`, `mytable2`.`name` SEPARATOR  ', ')
FROM `mytable1`
INNER JOIN `mytable2`
    ON `mytable2`.`id` = `mytable1`.`id`

What I'm getting:

127Name, 153Name, 153Name, etc..

What I'm trying to achieve:

127 : Name, 153 : Name, 153 : Name, etc..

Is there any way this is possible?

M Khalid Junaid · Accepted Answer · 2014-09-16 09:11:15Z

1

Use concat inside group_concat

GROUP_CONCAT(CONCAT(`mytable1`.`id`,' : ', `mytable2`.`name`) SEPARATOR  ', ')

answered Sep 16, 2014 at 9:11

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mySQL multiple columns in group concat

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related