The string_agg function in SQL is an aggregate function that concatenates (joins) strings from multiple rows into a single string with various options.
It’s particularly useful when you want to combine values from a column for each group of data in your reports, and it can make your query result easier to read, especially when dealing with many-to-many relationships. For example, if you want to create a report that shows all tags used in each topic, you could use string_agg to concatenate all the tags for each topic into a single string.
Syntax
Here’s the basic syntax of string_agg:
STRING_AGG(expression, delimiter)
The string_agg function takes two parameters:
expression: The value to be concatenated.delimiter: The delimiter to be inserted between strings. The delimiter can be a string or a character.
Example Usage In DE Queries
Let’s take a look at a few example queries that utilize string_agg
List All Categories on a Site
This query concatenates the name and id fields from the categories table into a single string, with each name and id pair separated by ’ : ', and each pair separated by ', '. The pairs are then ordered by id .
SELECT
-- The STRING_AGG function concatenates the 'name' and 'id' fields into a single string.
-- Each 'name' and 'id' pair is separated by ' : ', and each pair is separated by ', '.
-- The pairs are ordered by 'id'.
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- The data is selected from the 'categories' table.
Example Results:
| category_list |
|---|
| Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category |
List all Topics and Associated Tags
This query selects the id from the topics table and concatenates the associated tags for each topic into a single string. Each tag is separated by a comma and a space, and the tags are ordered alphabetically. The result is grouped by the topic id and ordered by the tags. If a topic doesn’t have any tags, the tags column will be NULL
SELECT t.id topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY t.id
ORDER BY tags
The result will be a list of formatted topic ids, each with a string of associated tags ordered alphabetically. For example:
Example Results:
| topic | tags |
|---|---|
| Welcome to the Lounge (3) | tag1, tag2 |
| A Very Safe for work post (3) | tag3, tag4, tag,5 |
| This is a blog tagged post (4) | tag3 |
| About the Lounge category (3) | tag5 |
| Welcome to your 14 day standard hosting trial! | NULL |
Detailed Explanation with Inline Comments
-- This SQL statement is selecting data from the 'topics' table and associated 'tags'.
SELECT
t.id topic_id, -- Select the 'id' from the 'topics' table.
-- The STRING_AGG function concatenates the associated 'tags' for each topic into a single string.
-- Each tag is separated by a comma and a space, and the tags are ordered alphabetically.
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- The data is selected from the 'topics' table.
-- The 'topic_tags' table is left joined on 'topic_id' from the 'topics' table.
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- The 'tags' table is left joined on 'tag_id' from the 'topic_tags' table.
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- The result is grouped by the topic 'id'.
ORDER BY
"tags" -- The result is ordered by the 'tags'.
Users Who Have Posted in Topics
This query will return a list of topic ids and titles, the usernames of the users who have posted in each topic, and the count of distinct users who have posted in each topic. The results are then ordered by the count of users, in descending order.
SELECT
topics.id AS "topic_id",
topics.title AS "topic_title",
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics
JOIN
posts ON posts.topic_id = topics.id
JOIN
users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title
ORDER BY
"user_count" DESC
Example Results
| topic | topic_title | users_posted | user_count |
|---|---|---|---|
| Lets make a topic (10) | Lets make a topic | anonymous, user1, user2, user3 | 4 |
| Another Topic (3) | Another Topic | user3, user4, user5 | 3 |
| Discobot test (2) | Discobot test | user6 | 1 |
Detailed Explanation with Inline Comments
-- This SQL statement is selecting data from the 'topics', 'posts', and 'users' tables.
SELECT
topics.id AS "topic_id", -- Select the 'id' from the 'topics' table.
topics.title AS "topic_title", -- Select the 'title' from the 'topics' table.
-- The STRING_AGG function concatenates the usernames of the users who have posted in the topic into a single string.
-- Each username is separated by a comma and a space, and the usernames are ordered alphabetically.
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- Count the number of distinct users who have posted in the topic.
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- The data is selected from the 'topics' table.
-- The 'posts' table is joined on 'topic_id' from the 'topics' table.
JOIN posts ON posts.topic_id = topics.id
-- The 'users' table is joined on 'id' from the 'posts' table.
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- The result is grouped by the topic 'id' and 'title'.
ORDER BY
"user_count" DESC -- The result is ordered by the number of users, in descending order.
That’s it for this tutorial!
If you have any questions or examples of how you’ve used string_agg , please feel free to share them below. 