SQL Server STRING_AGG Function

In this SQL Server tutorial, you will learn about the SQL Server STRIN_AGG function to add multiple values into a single string.

Also, you will understand the scenarios where you can use the STRING_AGG() function. Then syntax of the STRING_AGG() function.

Finally, you will learn how to use the STRING_AGG() function with null values and WITHIN GROUP clause to sort the result.

Table of Contents

SQL Server STRING_AGG Function

STRING_AGG function in SQL Server combines or concatenates values of multiple rows into a single string and separates the concatenated string with a separator.

But where the STRING_AGG() function can be beneficial, it can help in reporting and data visualization, data aggregation, and simplifying complex queries.

Sometimes, you need to make reports or visual representations of the data in your table. In that case, you can use the STRING_AGG() function to present the related data from multiple rows in a concise and readable format.
You can use the STRIN_AGG() to combine the data from the different rows of the table for a summary or overview.
If you need to concate the values, instead of using complex loops or cursors to concate those values, you can use the STRING_AGG(), which is a straightforward way to do that task.

These are some scenarios where the STRIN_AGG() function can be very effective. Next, let’s see the syntax of the STRING_AGG() function:

STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY expression)]

Where,

STRING_AGG(): The function to add the string values of multiple rows into a single string.
expression: It can be any type or column value that is converted to nvarchar and varchar implicitly before combining those values.
separator: The type varchar or nvarchar separator separates the concatenated string, for example, a hyphen (-) or any other string character.
WITHIN GROUP (ORDER BY expression): This one is an optional clause to order the values in ascending or descending order. By default, it orders the result in ascending order.

The return type of STRING_AGG() is the same as the input type. If you pass the expression as the varchar, nvarchar, then the function’s return type will be varchar, nvarchar.

But in the case of int, biting, small, money, datetime, etc, the return type is nvarchar. Let’s take an example and understand how STRIN_AGG() function works.

Suppose you can access the Employees table with columns EmployeeID, Name, and DepartmentID, shown below.

SQL Server STRING_AGG Function Employees Table

Now you need to list all the employees’ names in a department as a single string, which means for each department, you need to concate all the employees’ names that work in that department.

So use the below query.

SELECT DepartmentID, STRING_AGG(Name, ', ') AS Employees
FROM Employees
GROUP BY DepartmentID;

Executing the above query returns the result, which contains the two columns DepartmentID and Employees. As you can see, the employee’s names are concatenated according to their department as a single string separated by a comma (,).

For example, ‘Allen Smith, Bobby Johnson, David Williams’ is concatenated as a single string, separated by a comma, and belongs to a department ID equal to 1.

In the same way, the employee ‘Grace Wilson, Henry Moore’ is combined as a single string, separated by a comma, and belongs to the department ID equal to 3.

Let’s understand the query part STRING_AGG(Name, ‘, ‘),

Here, apply the STRING_AGG() function on the Name column that contains the names of the employees to combine the names of the employees as a single string.
Then ‘, ‘ is a (comma) separator that separates the combined names of the employees.
Also, using this query part GROUP BY DepartmentID, grouping each employee’s combined name according to their department ID.

This is how to combine the string values of multiple rows in a single string using the SQL Server STRING_AGG function.

SQL Server STRING_AGG Function without NULL Values

Sometimes, your table columns contain null values. In that case, you can ignore the null values and concatenate only the non-null values into a single string using the STRING_AGG() function.

Here, you don’t need to worry about null values. By default, the STRING_AGG() function ignores the null values.

Our employee’s table also contains some null values in a Name column, shown below.

SQL Server STRING_AGG Function Employees Table with Null Values

As you can see, there are two null values in the Name column of the Employees table, one null value for the employee in the department ID equal to 5 and the second one in the department ID equal to 7.

Let’s perform the same query that you have done in the above sections, where you need to combine the employee’s name into a single string for each department and then separate the combined string using the hyphen (-) separator.

For that, run the below query.

SELECT DepartmentID, STRING_AGG(Name, '-') AS Employees
FROM Employees
GROUP BY DepartmentID;

SQL Server STRING_AGG Function without NULL Values

As you can see, when you applied the STRING_AGG() function on the Name column containing the null values, the null values in the result set are not included for the employees whose department ID is equal to 5.

Also, each combined string for the department is separated by a hyphen(-), as shown in the above output.

But as you can see, department ID 6 shows only null values because there is only one employee in that department, but the name of that employee is missing or contains a NULL value for that employee.

If the same department contains an employee name and null values, it might show that employee’s name while ignoring the null values.

You must understand that passing the expression only null values will return the result as null. If the expression contains some values with null values, it will combine only the non-null values and ignore the null ones.

This is how to use the SQL Server STRING_AGG function without null values to combine only the non-null values into a single string.

SQL Server STRING_AGG Function with WITHIN GROUP Clause

You can use the optional clause WITHIN GROUP (ORDER BY expression) with STRING_AGG() function to sort the result in ascending (ASC) or descending (DESC) order.

First, I want you to execute the same query you have executed in the above query and notice the order of the combined string for each department.

SELECT DepartmentID, STRING_AGG(Name, '--') AS Employees
FROM Employees
GROUP BY DepartmentID;

SQL Server STRING_AGG Function without WITHIN GROUP Clause

Now look at the combined name of employees with a double hyphen (–) of the department with ID 1. The first name (Allen Smit) starts with the letter A, the second combined name (Bobby Johnson) starts with the letter B, and the third combined name (David Williams) starts with the letter D.

Here, the combined employee’s names are sorted in ascending order for each department, which means the string is ordered alphabetically from A to Z.

Suppose you want to change the order to descending from Z to A. Now execute this query to change the order of the string from ascending to descending.

SELECT DepartmentID, 
 STRING_AGG(Name, '--')  WITHIN GROUP (ORDER BY Name DESC) AS Employees
FROM Employees
GROUP BY DepartmentID;

SQL Server STRING_AGG Function with WITHIN GROUP Clause

In the above output of the query, you can see that it combined the employees’ names for each department and sorted them in descending order. For example, again, employees for the department ID with 1.

The first name is David Williams, which starts with the letter ‘D’; the second is Bobby Johnson, which starts with the letter ‘B’; and the third is Allen Smith, whose name starts with ‘A’.

Here, in the query part, WITHIN GROUP (ORDER BY Name DESC) means after combining the employee names (string) into a single string, sort the combined string by the Name column in descending order (DESC) alphabetically.

This is how to use the SQL Server STRING_AGG function to concatenate the multiple rows string values into a single string and sort them using the WITHIN GROUP clause.

Conclusion

In this SQL Server tutorial, you combined the multiple values of rows into a single string using the STRING_AGG() function in SQL Server. Also learned how to use this function with null values. Additionally, you sorted the combined string using the WITHIN GROUP clause with the STRING_AGG() function.