Often in PostgreSQL you may want to use the string_agg function to concatenate strings together and then use the ORDER BY statement to order the results of the concatenated strings.
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.
We use the string_agg() function to concatenate the strings into one string and we use the ORDER BY statement within this function to order the resulting strings in alphabetical order.
The following example shows how to use this syntax in practice.
Example: How to Use string_agg with Order 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.
Suppose we use the following syntax to do so:
SELECT team, string_agg(name, ', ') AS player_names
FROM athletes
GROUP BY team;
team | player_names
-------+------------------
Mavs | Doug, Chad, Matt
Hawks | Tony, Zach, Luke
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.
However, notice that the concatenated strings are shown in the order they appear in the table.
To instead sort the strings based on alphabetical order, we can use the ORDER BY statement within the string_agg function as follows:
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
The concatenated strings in the player_names column are now in alphabetical order.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Use string_agg with Distinct Values
PostgreSQL: How to Use string_agg with Multiple Columns
PostgreSQL: How to Find Position of Character in String
