# Using STRING\_AGG for Concise Query Results

**URL:** https://meta.discourse.org/t/using-string-agg-for-concise-query-results/278628
**Category:** Data & reporting
**Tags:** sql-tutorial
**Created:** [September 11, 2023, 11:43pm UTC](https://meta.discourse.org/t/using-string-agg-for-concise-query-results/278628 "2023-09-11T23:43:13Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [September 11, 2023, 11:43pm UTC](https://meta.discourse.org/t/using-string-agg-for-concise-query-results/278628/1 "2023-09-11T23:43:14Z")

</div>

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`:

```plaintext
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` .

```plaintext
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`

```sql
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**
>
> ```sql
> -- 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.

```sql
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**
>
> ```sql
> -- 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. 🙂