SQL Server STRING_AGG Function

Summary: in this tutorial, you will learn how to use the SQL Server STRING_AGG() function to concatenate rows of strings into one string with a specified separator.

Introduction to SQL Server STRING_AGG() function #

The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string.

Here’s the syntax of the STRING_AGG() function:

STRING_AGG ( expression, separator ) [ order_clause ]Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression is a value of any type that can be converted VARCHAR and NVARCHAR.
• separator is the separator for the result string. It can be a literal or variable.
• order_clause specifies the sort order of concatenated results using WITHIN GROUP clause.

Here’s the syntax of the order_clause:

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )Code language: SQL (Structured Query Language) (sql)

The STRING_AGG() function ignores NULL and does not add the separator for NULL when performing concatenation.

SQL Server STRING_AGG() function examples #

We will use the sales.customers table from the sample database for the demonstration:

1) Using STRING_AGG() function to generate email list #

This example uses the STRING_AGG() function to generate lists of emails of customers by the city:

SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;Code language: SQL (Structured Query Language) (sql)

Here is the partial result set: