groupConcat

Introduced in: v24.8.0

Calculates a concatenated string from a group of strings, optionally separated by a delimiter, and optionally limited by a maximum number of elements.

Syntax

groupConcat[(delimiter [, limit])](expression)

Aliases: group_concat, string_agg

Parameters

delimiter — A string that will be used to separate concatenated values. This parameter is optional and defaults to an empty string if not specified. String
limit — A positive integer specifying the maximum number of elements to concatenate. If more elements are present, excess elements are ignored. This parameter is optional. UInt*

Arguments

expression — The expression or column name that outputs strings to be concatenated. String
delimiter — A string that will be used to separate concatenated values. This parameter is optional and defaults to an empty string or delimiter from parameters if not specified. String

Returned value

Returns a string consisting of the concatenated values of the column or expression. If the group has no elements or only null elements, and the function does not specify a handling for only null values, the result is a nullable string with a null value. String

Examples

Basic usage without a delimiter

CREATE TABLE Employees (Name String) ENGINE = Memory;
INSERT INTO Employees VALUES ('John'), ('Jane'), ('Bob');

SELECT groupConcat(Name) FROM Employees;

JohnJaneBob

Using comma as a delimiter (parameter syntax)

SELECT groupConcat(', ')(Name) FROM Employees;

John, Jane, Bob

Using comma as a delimiter (argument syntax)

SELECT groupConcat(Name, ', ') FROM Employees;

John, Jane, Bob

Limiting the number of concatenated elements

SELECT groupConcat(', ', 2)(Name) FROM Employees;

John, Jane