AggregatePostgreSQL only

STRING_AGG

Joins values from many rows into one piece of text with a separator.

Syntax

STRING_AGG(<expression>, <separator>)
STRING_AGG(<expression>, <separator> ORDER BY <sort key>)

An aggregate function: it turns a group of rows into a single line of text. The order of elements isn't guaranteed on its own, so set it with ORDER BY right inside the brackets. In standard SQL a similar function is called LISTAGG.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

All names in one string

SELECT STRING_AGG(name, ', ') AS names

FROM employees;

names
Emma, Liam, Olivia, Noah, Sophia

A separate list for each department

SELECT department,

STRING_AGG(name, ', ' ORDER BY name) AS team

FROM employees

GROUP BY department

ORDER BY department;

department	team
backend	Emma, Liam
design	Sophia
frontend	Noah, Olivia

The ORDER BY in the brackets sets the order of names inside the string, and the one at the end of the query — the order of the departments themselves.

Watch out

It only accepts text: STRING_AGG(id, ', ') for a numeric id is an error. Cast the type: STRING_AGG(id::text, ', ').

NULL values are skipped silently, and if there are no rows at all, the result is NULL, not an empty string. If you need empty text, wrap it: COALESCE(STRING_AGG(…), '').

For unique values, write STRING_AGG(DISTINCT department, ', '). Then the ORDER BY inside can sort only by that same expression.

Related functions

ARRAY_AGGCONCAT_WS