PostgreSQL: How to Use string_agg with Multiple Columns

Often in PostgreSQL you may want to use the string_agg function to concatenate strings together, using multiple columns.

You can use the following syntax to do so:

SELECT team, string_agg(firstname || ' ' || lastname, ', ') AS player_names
FROM athletes
GROUP BY team;

Here is what this particular example does:

• First, we use the || operator to concatenate each corresponding string in the firstname and lastname columns.
• Then, we use the string_agg function to concatenate the resulting strings into a new column named player_names.
• Lastly, we group by the team column.

The following example shows how to use this syntax in practice.

Example: Use string_agg with Multiple Columns 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', 'Smith');
INSERT INTO athletes VALUES (0002, 'Mavs', 'Chad', 'Miller');
INSERT INTO athletes VALUES (0003, 'Mavs', 'Dean', 'Hanson');
INSERT INTO athletes VALUES (0004, 'Mavs', 'Mike', 'Gold');
INSERT INTO athletes VALUES (0005, 'Hawks', 'Tony', 'Fields');
INSERT INTO athletes VALUES (0006, 'Hawks', 'Zach', 'Hendrick');
INSERT INTO athletes VALUES (0007, 'Hawks', 'Luke', 'Jackson');
INSERT INTO athletes VALUES (0008, 'Hawks', 'Rob', 'Finch');

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | firstname | lastname 
-----------+-------+-----------+----------
         1 | Mavs  | Doug      | Smith
         2 | Mavs  | Chad      | Miller
         3 | Mavs  | Dean      | Hanson
         4 | Mavs  | Mike      | Gold
         5 | Hawks | Tony      | Fields
         6 | Hawks | Zach      | Hendrick
         7 | Hawks | Luke      | Jackson
         8 | Hawks | Rob       | Finch

Suppose that we would like to concatenate the first and last names of each player, grouped by team.

We can use the following syntax to do so:

SELECT team, string_agg(firstname || ' ' || lastname, ', ') AS player_names
FROM athletes
GROUP BY team;

 team  |                    player_names                     
-------+-----------------------------------------------------
 Mavs  | Doug Smith, Chad Miller, Dean Hanson, Mike Gold
 Hawks | Tony Fields, Zach Hendrick, Luke Jackson, Rob Finch

The resulting table has a new column named player_names that contains the first and last names of each player, grouped by team.

Feel free to use similar syntax with the string_agg function to concatenate strings from multiple columns in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Concatenate Strings Using GROUP BY
PostgreSQL: How to Use string_agg with Distinct Values
PostgreSQL: How to Find Position of Character in String