首页 > 代码库 > LISTAGG

LISTAGG

LISTAGG(measure_expr [, ‘delimiter‘])  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

 SELECT DEPARTMENT_ID "Dept",
           HIRE_DATE "Date",
           LAST_NAME "Name",
           LISTAGG(LAST_NAME, ‘; ‘) WITHIN GROUP(ORDER BY HIRE_DATE, LAST_NAME) OVER(PARTITION BY DEPARTMENT_ID) AS "Emp_list"
      FROM EMPLOYEES
     WHERE TO_CHAR(HIRE_DATE, ‘YYYY‘) < ‘2003‘
     ORDER BY "Dept", "Date", "Name";
 Dept Date        Name                      Emp_list
----- ----------- ------------------------- -------------------------
   30 2002/12/7   Raphaely                  Raphaely
   40 2002/6/7    Mavris                      Mavris
   70 2002/6/7    Baer                         Baer
   90 2001/1/13   De Haan                   De Haan
  100 2002/8/16   Faviet                     Faviet; Greenberg
  100 2002/8/17   Greenberg               Faviet; Greenberg
  110 2002/6/7    Gietz                       Gietz; Higgins
  110 2002/6/7    Higgins                   Gietz; Higgins

LISTAGG