While using LISTAGG during an ad hoc data investigation recently, I found Laurent Schneider's posting on double LISTAGG DISTINCT. He flattened the standard 14 records in the EMP table into a single record like so:

Nice, eh? Same results, but we also have aggregate amounts for salary and counts.

You may be asking: WHO CARES? WHY BOTHER?

Suppose I were researching which salaries are in which departments and associated with which jobs. It would be convenient to see both departments and jobs "flattened" onto a single record for the salary during analysis.

For the following example, I'll use EMP but double the number of employees, add another department, and change some job titles. That will help see the advantage of the double LISTAGG.