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