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