LISTAGG

This section contains reference documentation for the LISTAGG function.

Aggregates string values from rows into a single delimited string. An optional delimiter can be specified (defaults to comma). Use the optional DISTINCT keyword to include only distinct values.

Signature

LISTAGG(colName)

LISTAGG(colName, delimiter)

LISTAGG(DISTINCT colName, delimiter)

WITHIN GROUP clause

Use the WITHIN GROUP (ORDER BY ...) clause to control the order of values in the concatenated result. Without this clause the order of values is undefined.

LISTAGG(colName, delimiter) WITHIN GROUP (ORDER BY sortCol)

LISTAGG(DISTINCT colName, delimiter) WITHIN GROUP (ORDER BY sortCol)

The WITHIN GROUP clause requires the multi-stage query engine (v2). It was introduced in Apache Pinot 1.2.0 (#13146).

Only a single WITHIN GROUP clause is supported per query.

Usage Examples

These examples are based on the Batch Quick Start.

Basic aggregation

select LISTAGG(league, '/') AS value
from baseballStats
WHERE playerName = 'Barry Bonds'

value
NL/NL/NL/NL/NL/NL/NL/NL/NL/NL/...

Distinct values

select LISTAGG(DISTINCT league, ', ') AS value
from baseballStats
WHERE playerName = 'Barry Bonds'

value
NL, AL

Ordered aggregation with WITHIN GROUP

Concatenate carriers in alphabetical order for each origin–destination pair:

SELECT Origin, Dest,
  LISTAGG(DISTINCT Carrier, ', ') WITHIN GROUP (ORDER BY Carrier) AS carriers
FROM airlineStats
GROUP BY Origin, Dest

Origin	Dest	carriers
SFO	LAX	AA, DL, UA, …
JFK	BOS	B6, DL, …

Ordered aggregation with GROUP BY

SELECT teamID,
  LISTAGG(playerName, ' | ') WITHIN GROUP (ORDER BY playerName) AS players
FROM baseballStats
GROUP BY teamID

teamID	players
PIT	Barry Bonds | Bobby Bonilla | …