Mysql – Join two group_concat result

group-concatenationMySQL

this is the query: 

$sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group, 
        count(rtg.id) as totalGroups, 
        concat_ws(',', group_concat(rtg.group_name), group_concat(rtg.id)) as groupList
           from tbl_phone_numbers as pn
           left join tbl_rings_to_groups as rtg on rtg.user_id=pn.user_id
           where pn.user_id = ? and pn.status = ?";
$query = $this->db->query($sql, array($this->userId, 'Y'));

that returns the result of groupList like default,demo,1,2 but i want to get result something like :

default:1,demo:2

in MYSQL any suggestion??

Best Answer

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) 
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList
Related Question