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.
Recipe #1 - Aggregate multiple rows with LISTAGG
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
|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.