Listagg is a useful function to combine column values from different rows. In a sense, it is similar to sum() function that aggregates information across records. To show how listagg works, first we create a table that has record id, group id and words.
create table t(id number, gp number, word varchar2(128)); insert into t values(1,1,'Hello'); insert into t values(2,1,'World!'); insert into t values(3,2,'Good'); insert into t values(4,2,'Morning!');
select * from t; ID GP WORD 1 1 Hello 2 1 World! 3 2 Good 4 2 Morning!We can combine the words by group using listagg function.
select gp, listagg(word,' ') within group(order by id) from t group by gp order by gp; GP LISTAGG(WORD,'')WITHINGROUP(ORDERBYID) 1 Hello World! 2 Good Morning!