Often in PostgreSQL you may want to concatenate strings by group using the GROUP BY statement.
You can use the following syntax to do so:
SELECT team, string_agg(name, ', ' ORDER BY name) AS player_names
FROM athletes
GROUP BY team;
This particular example will concatenate the strings from the name column together into a new column named player_names using a comma as a separator.
Note that we use the string_agg() function to “aggregate” the strings into one string and we use the ORDER BY statement within this function to order the resulting strings in alphabetical order.
Lastly, we use the GROUP BY statement to group the strings together so that only the strings that share a column value in the team column are concatenated together.
The following example shows how to use this syntax in practice.
Example: Concatenate Strings Using GROUP BY in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
name TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 'Doug', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Chad', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Matt', 23);
INSERT INTO athletes VALUES (0004, 'Hawks', 'Tony', 29);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Zach', 14);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Luke', 33);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | name | points
-----------+-------+------+--------
1 | Mavs | Doug | 15
2 | Mavs | Chad | 12
3 | Mavs | Matt | 23
4 | Hawks | Tony | 29
5 | Hawks | Zach | 14
6 | Hawks | Luke | 33
Suppose that we would like to concatenate all of the strings from the name column together, grouping the results by the values in the team column.
We can use the following syntax to do so:
SELECT team, string_agg(name, ', ' ORDER BY name) AS player_names
FROM athletes
GROUP BY team;
team | player_names
-------+------------------
Hawks | Luke, Tony, Zach
Mavs | Chad, Doug, Matt
Notice that the resulting table contains a new column named player_names that contains the concatenated strings from the player column, grouped by the values in the team column.
Note #1: In the string_agg() function we specified that a comma followed by a space should be used as a separator. Feel free to specify a different separator if you would like.
Note #2: In the string_agg() function we used the ORDER BY statement to order the resulting concatenated strings in alphabetical order. Feel free to leave out this statement if you would like the strings to instead be concatenated based on the order that they appear in the table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Date from a Timestamp
