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

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

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.