LISTAGG

Syntax

LISTAGG
(
  expression  IN TEXT
)
RETURNS text;

LISTAGG
(
  expression  IN TEXT,
  delimite    IN TEXT
)
RETURNS text;

Overview

The LISTAGG function is a PostgreSQL implementation extension of Oracle's LISTAGG aggregate function. This function combines multiple text values within a group into a single string and returns it.

Parameters

Parameter	Description
expression	text type; the value to aggregate, ignoring it if it is NULL.
delimiter	text type; the delimiter to insert between each text value. If called without a delimiter, the internal

Example

# test table
create table employees ( first_name varchar, last_name varchar );

INSERT INTO employees (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson'),
('Emily', 'Davis'),
('David', 'Wilson'),
('Sarah', 'Brown'),
('James', 'Taylor'),
('Jessica', 'Martinez'),
('Daniel', 'Anderson'),
('Laura', 'Thomas');


# test 1
select oracle.listagg(last_name, ',') from employees ;

                               listagg                                
----------------------------------------------------------------------
 Doe,Smith,Johnson,Davis,Wilson,Brown,Taylor,Martinez,Anderson,Thomas
(1 row)

# test 2
select oracle.listagg(last_name) from employees ;
                           listagg                           
-------------------------------------------------------------
 DoeSmithJohnsonDavisWilsonBrownTaylorMartinezAndersonThomas
(1 row)