How to Use STRING_AGG() to Concatenate Strings in SQL Server?

In SQL Server, the STRING_AGG() will concatenate string values from multiple rows into a single string, which will use delimiters to separate the specific strings. It is very useful when you need to aggregate the text strings, like combining data together. 

Table of Contents:

• What is the STRING_AGG() Function in SQL Server?
• Examples to Use the STRING_AGG() Function in SQL Server
• Advantages of STRING_AGG() Function in SQL Server
• Conclusion

What is the STRING_AGG() Function in SQL Server? 

The STRING_AGG() function in SQL Server is one of the methods used to concatenate multiple rows into a single text string. The STRING_AGG() function uses separators to separate each string in a row. 

Syntax:

STRING_AGG(expression, separator) [ORDER BY expression [ASC | DESC]]

Examples to Use the STRING_AGG() Function in SQL Server

We can use STRING_AGG() function in SQL Server to get the details of the employees and products purchased to a single line text string with delimiters. 

Example 1: To get the name of the employee in each department

-------Create a table named employees
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Department NVARCHAR(50),
    EmployeeName NVARCHAR(100)
);
------Insert the value into employees table 
INSERT INTO Employees (ID, Department, EmployeeName)
VALUES 
    (1, 'HR', 'Kani'),
    (2, 'Content Writer', 'Vasu'),
    (3, 'HR', 'Ravi'),
    (4, 'HR', 'Sri'),
    (5, 'Content Writer', 'Rathore');
----Query to get the concatenated strings
SELECT STRING_AGG(EmployeeName, ', ') AS EmployeeNames
FROM Employees;

Output:

Explanation: The STRING_AGG() function fetches the employee name using the command SELECT STRING_AGG(EmployeeName, ‘, ‘) AS EmployeeNames and uses separators like “,” to separate each text string. 

Example 2: To get the list of products purchased in an order. 

----Creating the Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10,2),
    StockQuantity INT
);
-----Insert the value into the Product table
INSERT INTO Products (ProductName, Category, Price, StockQuantity)
VALUES
('Laptop', 'Electronics', 1200.00, 15),
('Mouse', 'Electronics', 25.50, 100),
('Keyboard', 'Electronics', 45.75, 50),
('Office Chair', 'Furniture', 150.00, 30),
('Monitor', 'Electronics', 300.00, 20),
('Desk', 'Furniture', 200.00, 10),
('HDMI Cable', 'Accessories', 15.99, 200);
----Query to fetch the concatenated strings 
SELECT Category, STRING_AGG(ProductName, ', ') AS ProductList  
FROM Products  
GROUP BY Category;

Output:

Explanation: The STRING_AGG() function separated each string by a separator. Then the GROUP BY aggregate function grouped each string into its respective category.

Advantages of STRING_AGG() Function in SQL Server

1. Unlike STUFF() with XML PATH, the STRING_AGG() function provides data in a simple, readable format. It makes the data very easy to understand. 
2. It has better performance compared to other methods as it optimizes the code. 
3. Supports the GROUP BY aggregate function. It uses that to group the rows or strings based on their category. 

Conclusion

The STRING_AGG() function is another method to concatenate multiple row values into a single text string. This method simplifies the query and makes it easier to read and understand. The STRING_AGG() function optimizes the code to improve its performance. 

Some Other Methods to Concatenate Text from Multiple Rows into a Single Text String

• Using COALESCE() Function in SQL Server
• Using SQL Server STUFF() Function
• Using FOR XML PATH clause in SQL Server
• Using CONCAT() function in SQL Server