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:

To sort the email list, you use the WITHIN GROUP clause:

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

The following picture shows the partial output:

2) Using STRING_AGG() function to generate a list of customer names separated into new lines

The following example uses the STRING_AGG() to generate a list of customer names for customers in the CA state separated by newlines:

SELECT
  STRING_AGG (CONCAT_WS (' ', first_name, last_name), CHAR(13)) customer_names
FROM
  sales.customers
WHERE
  state = 'CA';Code language: JavaScript (javascript)

Output:

customer_names
--------------
Kasha Todd
Tameka Fisher
Charolette Rice
Corene Wall
Jamaal Albert
...

If you use SQL Server Management Studio (SSMS), you must switch the output to text using the Ctrl-T keyboard shortcut to display the newlines properly.

In this example, we concatenate the first name, space, and last name using the CONCAT_WS() function and create a single output string using the STRING_AGG() function.

3) Using the STRING_AGG() function with the GROUP BY clause

The following statement retrieves the order id, the number of items in each order, and a list of product names for the corresponding order:

SELECT
  o.order_id,
  COUNT(*) item_count,
  STRING_AGG (p.product_name, ',') WITHIN GROUP (
    ORDER BY
      product_name
  ) product_list
FROM
  sales.orders o
  INNER JOIN sales.order_items i ON i.order_id = o.order_id
  INNER JOIN production.products p ON p.product_id = i.product_id
GROUP BY
  o.order_id;Code language: JavaScript (javascript)

The following example shows the partial output:

How it works.

• Join the orders, order_items, and products tables using the INNER JOIN clauses.
• Group orders by order id using the GROUP BY clause.
• Retrieve the number of items per order using the COUNT(*) function.
• Concatenate the product names of the order using the STRING_AGG() function.

Summary

• Use the STRING_AGG() function to concatenate rows of strings into one string with a specified separator.