Saturday, July 02, 2016

SQL Listagg Function

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!