Product Documentation

Appearance

STRING_AGG ​

Concatenate a set of character strings and insert a delimiter between each string. GROUP_CONCAT and LISTAGG are aliases for this function.

STRING_AGG([DISTINCT] expression, delimiter)
DISTINCT
Optionally, remove duplicates when concatenating the character strings. (The function alias GROUP_CONCAT_DISTINCT is also supported. If you use this alias, do not specify the DISTINCT keyword as well.)
expression
Specify a set of CHAR or VARCHAR values that will be concatenated.
delimiter
Specify a CHAR or VARCHAR literal value that is used as a separator.

Note that null values for expression and delimiter are ignored.

This function returns a VARCHAR(64000) data type.

This function does not support the WITHIN GROUP (ORDER BY) syntax.

Examples ​

Return a concatenated list of season names from the season table for specific seasons, using a comma and a space as the delimiter:

premdb=# select string_agg(season_name, ', ') from season where numteams=22;
          string_agg            
---------------------------------
 1992-1993, 1993-1994, 1994-1995
(1 row)

Concatenate all the strings from the season_name column in the season table and return the length of the concatenated string:

premdb=# select length(listagg(season_name,'|')) from season;
 length 
--------
   249
(1 row)

premdb=# select length(string_agg(season_name,'|')) from season;
 length 
--------
   249
(1 row)

premdb=# select length(group_concat(season_name,'|')) from season;
 length 
--------
   249
(1 row)

Note that all three function names return the same result.

Return a list of full-time scores for each home team in season 10:

premdb=# select team.htid, team.name, string_agg(ftscore, ', ') 
from match join team on match.htid=team.htid 
where seasonid=10 
group by team.htid, team.name 
order by team.htid;
 htid |       name        |                                          string_agg                                           
------+-------------------+-----------------------------------------------------------------------------------------------
   2 | Arsenal           | 3-2, 3-3, 1-1, 2-4, 2-1, 1-0, 4-3, 4-1, 2-0, 1-2, 4-0, 1-1, 3-1, 2-1, 1-3, 1-1, 3-0, 2-1, 2-0
   3 | Aston Villa       | 1-2, 2-0, 3-2, 1-0, 1-1, 2-1, 0-0, 2-0, 2-1, 0-1, 0-2, 1-2, 1-1, 0-0, 1-1, 2-1, 0-0, 1-1, 2-1
   6 | Blackburn Rovers  | 2-3, 3-0, 1-1, 4-1, 0-0, 0-1, 1-0, 3-0, 2-1, 1-2, 0-0, 1-1, 2-2, 0-1, 2-2, 2-0, 0-3, 2-1, 7-1
   8 | Bolton Wanderers  | 0-2, 3-2, 1-1, 0-0, 2-2, 1-3, 2-2, 0-0, 4-1, 0-3, 2-2, 2-1, 0-4, 1-0, 0-4, 0-1, 0-2, 1-1, 1-0
   13 | Charlton Athletic | 0-3, 1-2, 0-2, 1-2, 2-1, 1-0, 1-2, 1-1, 3-2, 0-2, 2-0, 0-2, 0-2, 0-0, 1-1, 1-1, 2-2, 3-1, 4-4
   14 | Chelsea           | 1-1, 1-3, 0-0, 5-1, 0-1, 2-1, 3-0, 3-2, 2-1, 2-0, 2-0, 4-0, 0-3, 2-2, 1-1, 2-4, 4-0, 4-0, 5-1
   17 | Derby County      | 0-2, 3-1, 2-1, 1-0, 1-1, 1-1, 3-4, 0-1, 1-3, 0-1, 2-3, 0-1, 2-2, 0-1, 2-3, 1-0, 0-1, 1-0, 0-0
   18 | Everton           | 0-1, 3-2, 1-2, 3-1, 0-3, 0-0, 1-0, 2-1, 1-2, 0-0, 2-2, 1-3, 0-2, 2-0, 1-3, 2-0, 1-0, 1-1, 5-0
   19 | Fulham            | 1-3, 0-0, 2-0, 3-0, 0-0, 1-1, 0-0, 2-0, 1-1, 0-0, 0-0, 0-2, 2-3, 2-1, 3-1, 2-1, 2-0, 0-2, 0-1
   21 | Ipswich Town      | 0-2, 0-0, 1-1, 1-2, 0-1, 0-0, 3-1, 0-0, 1-0, 1-2, 2-0, 0-6, 0-1, 1-0, 0-1, 1-3, 5-0, 2-1, 2-3
   22 | Leeds United      | 1-1, 1-1, 3-1, 0-0, 0-0, 0-0, 3-0, 3-2, 0-1, 2-0, 2-2, 0-4, 3-4, 1-0, 3-4, 2-0, 2-0, 2-1, 3-0
   23 | Leicester City    | 1-3, 2-2, 2-1, 0-5, 1-1, 2-3, 0-3, 0-0, 0-0, 1-1, 0-2, 1-4, 0-1, 1-2, 0-0, 0-4, 1-0, 2-1, 1-1
   24 | Liverpool         | 1-2, 1-3, 4-3, 1-1, 2-0, 1-0, 2-0, 1-1, 0-0, 5-0, 1-1, 1-0, 3-1, 2-0, 3-0, 1-1, 1-0, 1-0, 2-1
   26 | Manchester United | 0-1, 1-0, 2-1, 1-2, 0-0, 0-3, 5-0, 4-1, 3-2, 4-0, 1-1, 2-0, 0-1, 0-1, 3-1, 6-1, 4-1, 4-0, 0-1
   27 | Middlesbrough     | 0-4, 2-1, 1-3, 1-1, 0-0, 0-2, 5-1, 1-0, 2-1, 0-0, 2-2, 1-0, 1-2, 0-1, 1-4, 1-3, 2-0, 1-1, 2-0
   28 | Newcastle United  | 0-2, 3-0, 2-1, 3-2, 3-0, 1-2, 1-0, 6-2, 1-1, 2-2, 3-1, 1-0, 0-2, 4-3, 3-0, 3-1, 1-1, 0-2, 3-1
   37 | Southampton       | 0-2, 1-3, 1-2, 0-0, 1-0, 0-2, 2-0, 0-1, 1-1, 3-3, 0-1, 2-2, 2-0, 1-3, 1-1, 3-1, 2-0, 1-0, 2-0
   39 | Sunderland        | 1-1, 1-1, 1-0, 1-0, 2-2, 0-0, 1-1, 1-0, 1-1, 1-0, 2-0, 2-1, 0-1, 1-3, 0-1, 0-1, 1-1, 1-2, 1-0
   42 | Tottenham Hotspur | 1-1, 0-0, 1-0, 3-2, 0-1, 2-3, 3-1, 1-1, 4-0, 1-2, 2-1, 2-1, 1-0, 3-5, 2-1, 1-3, 2-0, 2-1, 1-1
   45 | West Ham United   | 1-1, 1-1, 2-0, 2-1, 2-0, 2-1, 4-0, 1-0, 0-2, 3-1, 0-0, 1-0, 1-1, 3-5, 1-0, 3-0, 2-0, 3-0, 0-1
(20 rows)

Run a similar query to the previous example, but specify that distinct scores are concatenated. The same score may only be listed once in the result of the function.

premdb=# select team.htid, team.name, string_agg(distinct ftscore, ', ') 
from match join team on match.htid=team.htid 
where seasonid=10 
group by team.htid, team.name 
order by team.htid;
 htid |       name        |                              string_agg                              
------+-------------------+----------------------------------------------------------------------
   2 | Arsenal           | 1-0, 1-1, 3-2, 1-3, 4-3, 4-0, 3-3, 3-1, 1-2, 2-0, 4-1, 3-0, 2-1, 2-4
   3 | Aston Villa       | 3-2, 1-1, 1-0, 1-2, 0-1, 2-0, 0-0, 0-2, 2-1
   6 | Blackburn Rovers  | 1-2, 2-3, 0-1, 2-2, 2-0, 4-1, 0-0, 3-0, 2-1, 1-1, 0-3, 7-1, 1-0
   8 | Bolton Wanderers  | 0-1, 4-1, 2-2, 2-1, 0-2, 0-0, 0-4, 1-3, 3-2, 1-1, 1-0, 0-3
   13 | Charlton Athletic | 0-0, 0-2, 2-1, 2-0, 2-2, 0-3, 3-2, 1-1, 1-0, 4-4, 3-1, 1-2
   14 | Chelsea           | 2-2, 2-0, 5-1, 3-0, 0-0, 2-4, 2-1, 0-3, 1-3, 3-2, 1-1, 4-0, 0-1
   17 | Derby County      | 3-1, 0-1, 2-3, 2-1, 3-4, 2-2, 0-0, 0-2, 1-0, 1-3, 1-1
   18 | Everton           | 0-2, 0-0, 5-0, 2-1, 2-2, 2-0, 1-3, 3-2, 1-1, 1-0, 0-3, 0-1, 3-1, 1-2
   19 | Fulham            | 3-1, 0-1, 2-3, 2-1, 2-0, 0-2, 0-0, 3-0, 1-3, 1-1
   21 | Ipswich Town      | 1-2, 2-3, 0-1, 3-1, 5-0, 0-0, 0-2, 2-0, 2-1, 0-6, 1-1, 1-0, 1-3
   22 | Leeds United      | 0-1, 3-1, 3-0, 0-0, 2-1, 3-4, 2-2, 2-0, 0-4, 3-2, 1-1, 1-0
   23 | Leicester City    | 1-0, 0-3, 0-4, 1-3, 1-1, 0-5, 0-1, 1-4, 2-3, 1-2, 2-1, 2-2, 0-0, 0-2
   24 | Liverpool         | 1-2, 3-1, 5-0, 2-1, 2-0, 3-0, 0-0, 1-1, 1-0, 1-3, 4-3
   26 | Manchester United | 6-1, 4-0, 1-2, 0-1, 3-1, 5-0, 0-0, 4-1, 2-0, 2-1, 1-0, 0-3, 1-1, 3-2
   27 | Middlesbrough     | 1-0, 1-3, 0-4, 1-1, 1-4, 0-1, 1-2, 2-1, 2-2, 2-0, 5-1, 0-0, 0-2
   28 | Newcastle United  | 6-2, 2-2, 0-2, 3-0, 2-1, 3-2, 1-1, 1-0, 4-3, 1-2, 3-1
   37 | Southampton       | 3-1, 0-1, 3-3, 1-2, 2-2, 2-0, 0-2, 0-0, 1-3, 1-1, 1-0
   39 | Sunderland        | 1-0, 1-1, 1-3, 0-1, 1-2, 0-0, 2-1, 2-2, 2-0
   42 | Tottenham Hotspur | 3-1, 0-1, 2-3, 1-2, 2-0, 0-0, 2-1, 1-3, 3-2, 1-1, 1-0, 3-5, 4-0
   45 | West Ham United   | 1-1, 1-0, 4-0, 3-5, 0-1, 3-1, 2-1, 2-0, 3-0, 0-0, 0-2
(20 rows)

Parent topic:Aggregate Functions