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:

firstname

lastname

department

Nic

Raboy

Engineering

Maria

Campos

Emergency Room

Nic

Raboy

Operations

Nic

Raboy

Design

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:

firstname

lastname

departments

Nic

Raboy

Design,Engineering,Operations

Maria

Campos

Emergency Room

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.

Nic Raboy

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