How to use group_concat()

SQL Tutorial Home

MySQL provides an aggregate function called group_concat, which can be used to concat several strings, from different rows separated by a specified separator. Due group_concat is an aggregate function you must use the group by clause in the query.

create table teams (team_name text, team_member text);
insert into teams values (‘Barcelona’,’Messi’), (‘Barcelona’,’Piquet’),(‘Barcelona’,’Xavi’);
insert into teams values (‘Real Madrid’,’Ronaldo’), (‘Real Madrid’,’Benzema’),(‘Real Madrid’,’Ramos’);

This is our base table:

select * from teams;
team_name team_member
"Real Madrid" "Ronaldo"
"Real Madrid" "Benzema"
"Real Madrid" "Ramos"
"Barcelona" "Messi"
"Barcelona" "Piquet"
"Barcelona" "Xavi"

This function concatenates the values of a given column using a separator, due string_agg is an aggregate function, the group by clause plays an important role to define what rows are grouped.

select team_name, group_concat(team_member) from teams group by team_name;
team_name string_agg(team_member,’, ‘)
"Real Madrid" "Ronaldo, Benzema, Ramos"
"Barcelona" "Messi, Piquet, Xavi"

The group by team_name clause creates groups of rows with the same value in team_name, then all the members of the same team are concatenated in the same string.

IN THIS PAGE