SQL SERVER – STRING_AGG Function to Concatenate Strings

During the recent consulting engagement of Comprehensive Database Performance Health Check, I noticed that my client is using a very complex logic for contacting strings from a table. We were able to improve the performance of the query by using STRING_AGG Function to Concatenate Strings.

In SQL Server, data manipulation often involves aggregating and combining values from multiple rows into a single string. Before SQL Server 2017, developers and database administrators had to rely on complex workarounds to achieve this. However, with the introduction of the STRING_AGG function in Transact-SQL, the process of concatenating strings has become much simpler and more efficient.

Understanding the STRING_AGG Function:

The STRING_AGG function is an aggregate function used to concatenate the values from a column into a single string. It is particularly useful when you want to transform multiple rows of data into a single, comma-separated list or any other delimiter of your choice. The function takes two arguments: the column from which to retrieve the values and the separator that will be used to join these values.

The syntax is very simple.

STRING_AGG (expression, separator)

Example

Let’s look at a practical example to better understand how the STRING_AGG function works. Suppose we have a table named Fruits with the following data:

FruitName
---------------------
Apple
Banana
Orange
Mango

We want to concatenate the fruit names into a single comma-separated string. Here’s how we can achieve this using the STRING_AGG function:

SELECT STRING_AGG(FruitName, ', ') AS ConcatenatedFruits
FROM Fruits;

The result will be:

ConcatenatedFruits
---------------------------------
Apple, Banana, Orange, Mango

As you can see, the STRING_AGG function seamlessly combined the fruit names into a single string, separated by commas.

Grouping Data

The STRING_AGG function can also be used with the GROUP BY clause to concatenate values based on groups. This is particularly helpful when you have a relational table with data that needs to be grouped and aggregated.

Let’s consider a table named Orders, which contains data about customer orders:

If we want to concatenate the products for each customer, we can use the following query:

SELECT CustomerID, STRING_AGG(Product, ', ') AS ConcatenatedProducts
FROM Orders
GROUP BY CustomerID;

The result will be:

Conclusion

The STRING_AGG function in Transact-SQL provides an elegant and efficient solution for concatenating strings in SQL Server. It simplifies the process of aggregating data into a single string, making it a valuable tool for developers and database administrators alike. Whether you need to create comma-separated lists or concatenate values based on groups, the STRING_AGG offers flexibility and convenience, enhancing your SQL Server querying capabilities.

By using this powerful function, you can efficiently handle string concatenation tasks, leading to more concise and readable SQL queries while improving the overall performance of your applications.

You can always reach out to me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)