SQL

Aggregate multiple rows into one as a comma-delimted list


Problem

Aggregate multiple rows into one as a comma-delimted list

If you worked with SQL for a while, you'll probably have had the desire to combine multiple rows into a single one to get a better overview. That's something lots of people have done using the language where which they ran their SQL queries. Luckily, Oracle provides a number of solutions to this.


Solution

Recipe #1 - Aggregate multiple rows with LISTAGG

ENAME DEPTNO
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

If you look at this list, it's hard to see how many people are in a department. You'd have to check every line to do that, but let's have a look at the following statement:

SELECT 
  deptno, 
  listagg(ename, ', ') within GROUP (ORDER BY ename) employees, 
  COUNT(*) number_of_employees 
FROM 
  emp
GROUP BY 
  deptno
DEPTNO EMPLOYEES NUMBER_OF_EMPLOYEES
10 CLARK, KING, MILLER 3
20 ADAMS, FORD, JONES, SCOTT, SMITH 5
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 6

The aggregate function listagg is only available in 11g or later. If you're using an older version of Oracle, have a look at wm_concat, it's an undocument function, but it works well and is probably used in more places than it should.

Comments




Please sign-in to post a comment