How to Use STRING_AGG() in SQL Server with Custom Separators and Sorting

In SQL Server, STRING_AGG() is an aggregate function that concatenates string values from a group into a single string. It’s a handy tool for doing things like creating comma-separated lists from related data.

In this article we’ll check out how to use STRING_AGG() with different separators. We’ll also see how we can control the order of the concatenated strings.

Setup and Data

First, let’s create a couple of tables and populate them with some data. These will be used by the examples that follow:

-- Create a table to store book information
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INT
);

-- Insert some sample book data
INSERT INTO Books (BookID, Title, AuthorID) VALUES
(1, 'The Galactic Explorer', 101),
(2, 'Aetherium Rising', 102),
(3, 'Whispers of the Void', 101),
(4, 'The Last Starship', 103),
(5, 'Chronicles of Xylos', 102),
(6, 'Echoes of the Fading Sun', 101);

-- Create a table to store author information
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Insert some sample author data
INSERT INTO Authors (AuthorID, FirstName, LastName) VALUES
(101, 'Elara', 'Vance'),
(102, 'Kaelen', 'Thorne'),
(103, 'Lyra', 'Sterling');

Examples of `STRING_AGG()`

Here are a few examples demonstrating how to use STRING_AGG() with different separators and sorting options.

Example 1: Basic Concatenation with a Comma Separator

Our first example demonstrates what is probably the most common use case, which is creating a comma separated list. In this case we’re creating a comma-separated list of book titles for each author. This can be useful for summarizing an author’s bibliography in a single row.

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    STRING_AGG(B.Title, ', ') AS BooksWritten
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Result:

AuthorName     BooksWritten                                                         
-------------  ---------------------------------------------------------------------
Lyra Sterling  The Last Starship                                                    
Kaelen Thorne  Aetherium Rising, Chronicles of Xylos                                
Elara Vance    The Galactic Explorer, Whispers of the Void, Echoes of the Fading Sun

Here, the query joins the Authors and Books tables on AuthorID. It then groups the results by author’s full name. The STRING_AGG() function, with a separator of ', ', concatenates all the Title values for each author group. This results in a list of book titles for each author, separated by a comma and a space.

Note that we appended a space after the comma in order to have a space between the separator and the start of the next book.

Example 2: Concatenation with a Pipe Separator

This example uses a pipeline character (|) as the separator. This can be helpful when the data itself might contain commas, preventing ambiguity:

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    STRING_AGG(B.Title, ' | ') AS BooksWritten
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Result:

AuthorName     BooksWritten                                                           
-------------  -----------------------------------------------------------------------
Lyra Sterling  The Last Starship                                                      
Kaelen Thorne  Aetherium Rising | Chronicles of Xylos                                 
Elara Vance    The Galactic Explorer | Whispers of the Void | Echoes of the Fading Sun

Example 3: Concatenation with Sorting

This example demonstrates how to sort the concatenated strings. This allows us to present lists in a logical order, such as alphabetically. Without the WITHIN GROUP (ORDER BY ...) clause, the order of the concatenated strings is not guaranteed.

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    STRING_AGG(B.Title, ' -> ') WITHIN GROUP (ORDER BY B.Title ASC) AS BooksWritten
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Output:

AuthorName     BooksWritten                                                             
-------------  -------------------------------------------------------------------------
Lyra Sterling  The Last Starship                                                        
Kaelen Thorne  Aetherium Rising -> Chronicles of Xylos                                  
Elara Vance    Echoes of the Fading Sun -> The Galactic Explorer -> Whispers of the Void

The WITHIN GROUP (ORDER BY B.Title ASC) clause tells STRING_AGG() to first sort the book titles for each author alphabetically before concatenating them. This ensures that the output list is consistently ordered, which can make the result more predictable and easier to read. In this case I used ' -> ' for the separator, but this could be anything of course.

Example 4: Sorting by a Different Column

This example shows that we can sort the concatenated strings based on a column other than the one being concatenated. This can be useful when you want to order a list by a specific metric, such as a publication date (not in our example, but a common use case) or, in this case, the BookID.

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    STRING_AGG(B.Title, '; ') WITHIN GROUP (ORDER BY B.BookID ASC) AS BooksWritten
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Result:

AuthorName     BooksWritten                                                         
-------------  ---------------------------------------------------------------------
Lyra Sterling  The Last Starship                                                    
Kaelen Thorne  Aetherium Rising; Chronicles of Xylos                                
Elara Vance    The Galactic Explorer; Whispers of the Void; Echoes of the Fading Sun

In this query, the STRING_AGG() function concatenates the Title values, but the WITHIN GROUP (ORDER BY B.BookID ASC) clause sorts them by BookID. This effectively orders the books by their insertion order in the table, or by whatever the BookID represents. This shows the flexibility of the WITHIN GROUP clause, as the ordering column does not have to be the same as the column being aggregated. And just for variety’s sake, we used '; ' as the separator.

Example 5: Using HTML Tags as a Separator

There’s nothing to stop us from using HTML tags as the separator. This can be useful if we need to include the aggregated list into an HTML document. For example, we could use <br> (break) tag as a separator. This HTML tag will present each book on a new line when rendered in a browser.

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    STRING_AGG(B.Title, '<br>') WITHIN GROUP (ORDER BY B.Title ASC) AS BooksWritten
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Output:

AuthorName     BooksWritten                                                             
-------------  -------------------------------------------------------------------------
Lyra Sterling  The Last Starship                                                        
Kaelen Thorne  Aetherium Rising<br>Chronicles of Xylos                                  
Elara Vance    Echoes of the Fading Sun<br>The Galactic Explorer<br>Whispers of the Void

However, while the <br> element does work (and is often used), it’s not the semantically correct way to present lists in HTML.

We should use a list element to present lists in HTML. In our case, we can use an unordered list:

SELECT
    A.FirstName + ' ' + A.LastName AS AuthorName,
    '<ul>' + STRING_AGG('<li>' + B.Title + '</li>', '') WITHIN GROUP (ORDER BY B.Title ASC) + '</ul>' AS BookList
FROM
    Authors AS A
JOIN
    Books AS B ON A.AuthorID = B.AuthorID
GROUP BY
    A.FirstName, A.LastName;

Output:

AuthorName     BookList                                                                                             
-------------  -----------------------------------------------------------------------------------------------------
Lyra Sterling  <ul><li>The Last Starship</li></ul>                                                                  
Kaelen Thorne  <ul><li>Aetherium Rising</li><li>Chronicles of Xylos</li></ul>                                       
Elara Vance    <ul><li>Echoes of the Fading Sun</li><li>The Galactic Explorer</li><li>Whispers of the Void</li></ul>

This query does a few things to construct the full HTML list:

The STRING_AGG() function concatenates the strings. The separator is an empty string ('') because we’re manually adding the <li> and </li> tags to each book title.
Each book title is wrapped in <li> tags before being aggregated: '<li>' + B.Title + '</li>'.
The WITHIN GROUP (ORDER BY B.Title ASC) clause ensures the list items are sorted alphabetically.
Finally, the entire concatenated string is wrapped in <ul> and </ul> tags. This creates a complete and valid HTML unordered list that’s ready to be used in a web page.

So hopefully that’s given you some ideas about how you can use SQL Server’s STRING_AGG() function to your advantage in future projects.

Setup and Data

Examples of STRING_AGG()

Example 1: Basic Concatenation with a Comma Separator

Example 2: Concatenation with a Pipe Separator

Example 3: Concatenation with Sorting

Example 4: Sorting by a Different Column

Example 5: Using HTML Tags as a Separator

Examples of `STRING_AGG()`