Function level 501 (PI70535 - May 2017)

Example

Produce an alphabetical list of comma-separated names, grouped by department, from the sample employee table.

SELECT WORKDEPT,
 LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
 AS EMPLOYEES
 FROM EMP
 GROUP BY WORKDEPT;

The following result is returned.

---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
WORKDEPT  EMPLOYEES                                                             
---------+---------+---------+---------+---------+---------+---------+---------+---------+-------
A00       HAAS, HEMMINGER, LUCCHESI, O'CONNELL, ORLANDO                         
B01       THOMPSON                                                              
C01       KWAN, NATZ, NICHOLLS, QUINTANA                                        
D11       ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER, YAMAMOTO, YOSHIMURA
D21       JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH         
E01       GEYER                                                                 
E11       HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER     
E21       ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG