Aggregate SQL Data From Multiple Rows Into A Single Row

Recently I found myself needing to aggregate my multiple row SQL data into a single row. This was because the parent data was more useful to me than the multiple row’d data, but I still needed to include it because it was still an essential part of my report. The data looked something like the following:

After searching the Oracle documentation I came across the LISTAGG function which took care of exactly what I needed.

select
    firstname,
    lastname,
    listagg(department, ',') within group (order by department) as departments
from tbl_people
group by
    firstname,
    lastname;

Running the above query gave me a result set that looked like the following:

Just like that I was able to see all the departments each one of my people were a part of.

Now there could be a scenario for whatever reason, maybe bad data, where there may be duplication and a person falls into a department more than once. Using an Oracle regular expression like below, you can get rid of all the duplicate entries.

select regexp_replace('1,1,2,3,3,3,4', '([^,]+)(,\1)+', '\1')) from dual;

Now there is another function for Oracle that worked equally well, if not better. XMLAgg will do the same task and may play nicer with longer strings of data.

select
    p.firstname,
    p.lastname,
    (select xmlagg(xmlelement(E, d.departmentname || ';')).extract('//text()') from tbl_departments d where d.person_id = p.person_id)
from tbl_people p;

The above code will work with two tables. All of the person’s departments will be concatenated into a single column for each row of person data.

If you ever find yourself needing to work with multiple rows of data, maybe in a sub-query, LISTAGG and XMLAGG are great functions for the job.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.