When you are working with SQL Server, you might encounter the requirement to convert multiple rows into a single comma-separated value (CSV). This is particularly useful when you are aggregating your data, reporting, or formatting the query results for display.
In this blog, we are about to explore various methods to achieve this by using various functions such as STRING_AGG(), STRING_ESCAPE, and FOR XML PATH clause.
Table of Contents
Why Convert Multiple Rows to a Comma-Separated Value?
When you convert multiple data rows into a single row with comma-separated values (CSV), it aggregates all the related information. This simplifies your data for reports by creating summaries of the data, for system compatibility and database denormalization. It packs multiple values into a single cell which improves data transfer and query efficiency. It also gives you a user-friendly display by making data more readable in applications.
Various Methods to Convert Multiple Rows to CSV
Method 1: Using STRING_AGG() (SQL Server 2017+)
The STRING_AGG() function is one of the easiest and most effective ways to concatenate values.
For example:
-- Create the Product table
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY(1,1),
Category VARCHAR(50),
ProductName VARCHAR(50)
);
-- Insert sample data
INSERT INTO Products (Category, ProductName) VALUES
('Fruits', 'Apple'),
('Fruits', 'Banana'),
('Fruits', 'Mango'),
('Vegetables', 'Carrot'),
('Vegetables', 'Spinach'),
('Vegetables', 'Cabbage');
-- Query to convert multiple rows into a comma-separated list
SELECT Category, STRING_AGG(ProductName, ', ') AS ProductList
FROM Products
GROUP BY Category;
Query:
SELECT STRING_AGG(ProductName, ', ') AS CSV_Result
FROM Products;
Output:
Method 2: Using FOR XML PATH (Pre-SQL Server 2017)
If you are using an older version SQL Server, you can use FOR XML PATH (‘ ’) to concatenate rows with commas.
SELECT STUFF(
(SELECT ', ' + ColumnName
FROM TableName
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
1, 2, ''
) AS CSV_Result;
FOR XML PATH (‘ ’) concatenates the rows with XML tags and STUFF() removes the leading comma and space.
Method 3: Using STRING_ESCAPE with STRING_AGG (for JSON output)
While converting multiple rows to one comma-separated value, if there is a requirement for escape special characters such as quotes or line breaks, you can use STRING_ESCAPE() with STRING_AGG(). It is mostly used when you are working with JSON-based applications.
SELECT STRING_AGG(STRING_ESCAPE(ProductName, 'json'), ', ') AS CSV_Result
FROM Products;
Output:
Method 4: Using COALESCE
If you cannot use STRING_AGG() or FOR XML PATH (), you can use COALESCE () in a loop-based approach. This will allow you to progressively build a string by adding each value from a result one by one.
For example,
DECLARE @CSV_Result NVARCHAR(MAX) = '';
SELECT @CSV_Result = COALESCE(@CSV_Result + ', ', '') + ProductName
FROM Products;
SELECT @CSV_Result AS CSV_Result;
Output:
Conclusion
For the best performance of string aggregation in SQL Server 2017 and later, you can use STRING_AGG() for better performance. In older SQL Server versions, FOR XML PATH() is a good alternative. If you are generating JSON output to ensure data integrity, you can use STRING_ESCAPE() to handle special characters. COALESCE() can be used for broader compatibility across different SQL Server versions.
FAQs
1. Can I use STRING_AGG() with multiple columns?
Yes, you can concatenate columns inside STRING_AGG():
SELECT STRING_AGG(FirstName + ‘ ‘ + LastName, ‘, ‘) AS CSV_Result
FROM Employees;
2. How can I order values in the comma-separated list?
You can use ORDER BY inside STRING_AGG():
SELECT STRING_AGG(ColumnName, ‘, ‘) WITHIN GROUP (ORDER BY ColumnName) AS CSV_Result
FROM TableName;
3. Can I use STRING_AGG() with multiple columns?
Yes, you can concatenate columns inside STRING_AGG():
SELECT STRING_AGG(FirstName + ‘ ‘ + LastName, ‘, ‘) AS CSV_Result
FROM Employees;