3. Elements of SQL Statements : SQL Functions : Aggregate Functions : String Aggregate Functions
 
Share this page                  
String Aggregate Functions
String aggregate functions concatenate a set of string values.
LISTAGG
LISTAGG ([DISTINCT] value_expr [, 'delimiter'])
    [WITHIN GROUP (order-by-clause)]
Concatenates expressions within a group.
Result type: VARCHAR(4000). Longer strings are truncated. The length of the result can be configured using the [engine] listagg_group_maxlen parameter.
where:
DISTINCT
Eliminates duplicate values.
value_expr
Specifies an expression that can appear in the select list of a query. Can contain constants, row values, operators, scalar functions, and scalar subqueries. Null values in the value_expr column are ignored. The value is cast to char data type before being concatenated.
'delimiter'
Defines the separator between concatenated items. The default separator is the empty string.
WITHIN GROUP (order-by-clause)
Specifies how the items in the result should be sorted.
The following LISTAGG examples are based on this table:
DEPT             EMPNO FIRSTNAME  LASTNAME
----------- ---------- ---------- ----------
Marketing          101 Douglas    Cray
Marketing          103 Dong       Luang
Marketing          105 Dennis     Indolay
Admin              107 Sherry     Keller
Admin              109 Carl       Nader
Admin              111 Ruth       Turret
Sales              113 Andrew     Bonnet
Sales              115 Oscar      Hender
Sales              117 Justin     Braushere 
Sales              119            Smith
Concatenate the names of all employees, ordered by last name (LISTAGG used as a simple aggregate):
SELECT LISTAGG(lastname)
    WITHIN GROUP (ORDER BY lastname) AS PERSONNEL
    FROM employee;
PERSONNEL
-----------------------------------------------------------
BonnetBraushereCrayHenderIndolayKellerLuangNaderSmithTurret
Same as previous query, but use a delimiter to separate names with a comma:
SELECT LISTAGG(lastname, ',')
    WITHIN GROUP (ORDER BY lastname) AS PERSONNEL
    FROM employee;
PERSONNEL
--------------------------------------------------------------------
Bonnet,Braushere,Cray,Hender,Indolay,Keller,Luang,Nader,Smith,Turret
Concatenate employees in each department, ordered by last name (LISTAGG used as a regular aggregate with GROUP BY):
SELECT dept, LISTAGG(lastname, ',')
    WITHIN GROUP (ORDER BY lastname) AS PERSONNEL
    FROM employee GROUP BY dept;
DEPT        PERSONNEL
----------- ------------------------------
Admin       Keller,Nader,Turret
Marketing   Cray,Indolay,Luang
Sales       Bonnet,Braushere,Hender,Smith
Concatenate employee names in the same department, ordered by last name, partitioned by department (LISTAGG used as a windowing aggregate):
SELECT dept, firstname, lastname, LISTAGG(lastname, ',')
    WITHIN GROUP(ORDER BY lastname)
    OVER (PARTITION BY dept) AS PERSONNEL
    FROM employee ORDER BY dept, lastname, firstname;
DEPT        FIRSTNAME  LASTNAME   PERSONNEL IN SAME DEPT
----------- ---------- ---------- ------------------------------
Admin       Sherry     Keller     Keller,Nader,Turret
Admin       Carl       Nader      Keller,Nader,Turret
Admin       Ruth       Turret     Keller,Nader,Turret
Marketing   Douglas    Cray       Cray,Indolay,Luang
Marketing   Dennis     Indolay    Cray,Indolay,Luang
Marketing   Dong       Luang      Cray,Indolay,Luang
Sales       Andrew     Bonnet     Bonnet,Braushere,Hender,Smith
Sales       Justin     Braushere  Bonnet,Braushere,Hender,Smith
Sales       Oscar      Hender     Bonnet,Braushere,Hender,Smith
Sales                  Smith      Bonnet,Braushere,Hender,Smith