How to Use listagg()

Oracle provides a function called listagg, which is used to concat all the strings grouped in each set of rows created by the group by clause. Values are separated by a specified delimiter character.

create table teams (team_name varchar(20), team_member varchar(20));
insert into teams values (‘Barcelona’,’Messi’);
insert into teams values (‘Barcelona’,’Piquet’);
insert into teams values (‘Barcelona’,’Xavi’);
insert into teams values (‘Real Madrid’,’Ronaldo’); 
insert into teams values (‘Real Madrid’,’Benzema’);
insert into teams values (‘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 LISTAGG is an aggregate function, the group by clause plays an important role to define what rows are grouped.

select team_name, 
    LISTAGG(team_member, ',') within group (order by team_member) as players
from   teams
group by team_name;
team_name string_agg(team_member,’, ‘)
Real Madrid Ronaldo, Benzema, Ramos
Real Madrid 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