When working with SQL Server, you may encounter scenarios where you need to transform multiple rows of data into a single string of comma-separated values. This is especially useful in situations like report generation, aggregating results for display, or preparing data for export. The ability to represent a set of values in a single row or cell helps streamline output, makes results more readable, and improves overall data presentation in client applications. SQL Server provides several methods to accomplish this task, with the available options depending largely on the version of SQL Server you are using. This article explores different approaches to convert multiple rows into a comma-separated value, focusing on SQL Server’s built-in functions and techniques suitable for different environments. We will examine key SQL Server functionalities like STRING_AGG(), FOR XML PATH, STRING_ESCAPE, and COALESCE, and show how each can be implemented to achieve this conversion effectively.
Understanding the Need for Row-to-CSV Conversion
Converting multiple data rows into a single comma-separated value is not just about transforming the data structure. It serves several real-world use cases that make this feature essential in many applications. This transformation allows you to present summarized information clearly, improves compatibility with systems that expect delimited data formats, and helps in denormalizing databases for reporting purposes. When large sets of relational data need to be displayed in a user-friendly format, aggregating them into a single comma-separated list simplifies the interface. Applications such as dashboards, reports, and exported flat files often require such aggregation to reduce data clutter. It is also useful in data transmission scenarios where each field must contain all related values in one place to preserve relationships without requiring joins on the client side.
Preparing the Sample Dataset
To effectively demonstrate the methods of converting multiple rows to a comma-separated value, we will first create a sample table named Products. This table will include product categories and their respective product names.
sql
CopyEdit
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY(1,1),
Category VARCHAR(50),
ProductName VARCHAR(50)
);
INSERT INTO Products (Category, ProductName) VALUES
(‘Fruits’, ‘Apple’),
(‘Fruits’, ‘Banana’),
(‘Fruits’, ‘Mango’),
(‘Vegetables’, ‘Carrot’),
(‘Vegetables’, ‘Spinach’),
(‘Vegetables’, ‘Cabbage’);
This dataset helps illustrate how to group and concatenate product names by category into a single string using different SQL Server techniques.
Using STRING_AGG() for Row-to-CSV Conversion
One of the most efficient ways to convert multiple rows to a comma-separated value in SQL Server 2025 and later is by using the STRING_AGG() function. This aggregate function allows for easy string concatenation within grouped queries. Unlike earlier workarounds that involved XML or string manipulation, STRING_AGG() simplifies the entire process into a single, readable query.
Basic Syntax of STRING_AGG
The STRING_AGG() function combines the values of a column across rows into a single string, separated by a specified delimiter. Its syntax is straightforward to understand:
sql
CopyEdit
SELECT STRING_AGG(ColumnName, ‘, ‘) AS ResultAlias FROM TableName;
This syntax takes the values from ColumnName, adds a comma and a space between them, and aggregates them into one string labeled as ResultAlias.
Applying STRING_AGG to Grouped Data
One of the main use cases is grouping related rows and converting them into a comma-separated list per group. For instance, you can group products by category and list the product names separated by commas using the following query:
sql
CopyEdit
SELECT Category, STRING_AGG(ProductName, ‘, ‘) AS ProductList
FROM Products
GROUP BY Category;
This query returns a result set with one row per category. Each row includes the category name and a single string containing all product names in that category separated by commas.
Example Output
Assuming the sample data provided earlier, this query would produce the following result:
nginx
CopyEdit
Category ProductList
Fruits Apple, Banana, Mango
Vegetables Carrot, Spinach, Cabbage
Aggregating Without a Grouping
You can also apply STRING_AGG() to all rows without grouping if you simply want a single comma-separated string of all values in a column:
sql
CopyEdit
SELECT STRING_AGG(ProductName, ‘, ‘) AS CSV_Result FROM Products;
This query returns one row with a single string:
nginx
CopyEdit
CSV_Result
Apple, Banana, Mango, Carrot, Spinach, Cabbage
Benefits of Using STRING_AGG
The STRING_AGG() function is the most modern and straightforward method for concatenating row values into a single string. It is native to SQL Server 2025 and later, which means it offers better performance, cleaner syntax, and avoids the complexity of older workarounds. It does not require additional functions like STUFF() or FOR XML PATH, making it easier to read and maintain. Since it supports grouping, it is extremely flexible for building aggregated lists within reports and summaries. It also integrates well with subqueries and common table expressions (CTEs), enhancing its usability in complex SQL scenarios.
Limitations of STRING_AGG
While STRING_AGG() is powerful, it has limitations. It is not available in SQL Server versions before 2025, which means it cannot be used in legacy systems. Also, special character handling is limited unless paired with functions like STRING_ESCAPE() when outputting to formats like JSON. Furthermore, the delimiter provided to STRING_AGG() cannot dynamically vary per row; it must be constant across the query. Lastly, if the aggregated result exceeds the maximum size of a VARCHAR(MAX) or NVARCHAR(MAX) field, the output may be truncated or result in an error depending on the data.
Using FOR XML PATH for Row-to-CSV Conversion in Older SQL Server Versions
Before SQL Server 2025 introduced the STRING_AGG() function, one of the most popular methods for converting multiple rows into a single comma-separated string was the use of FOR XML PATH. This technique remains highly useful for compatibility with older versions of SQL Server, such as 2008, 2012, 2014, and 2016. It involves combining string concatenation with XML functionality to achieve the desired aggregation.
Understanding the FOR XML PATH Technique
The FOR XML PATH(”) method works by leveraging SQL Server’s XML rendering capabilities. When used in a subquery, it treats each concatenated string as XML and then collapses it into a single output string. The empty string as the PATH argument (”) ensures that there are no XML tags in the output. This method allows developers to concatenate values from multiple rows into one.
Basic Syntax of FOR XML PATH
The syntax involves a subquery or a correlated subquery that uses FOR XML PATH(”), optionally wrapped in STUFF() to clean up the leading comma:
sql
CopyEdit
SELECT STUFF((
SELECT ‘, ‘ + ColumnName
FROM TableName
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”);
The STUFF() function removes the first comma and space from the resulting string by deleting the first two characters. The TYPE directive ensures that special characters are handled safely, and .value(‘.’, ‘NVARCHAR(MAX)’) converts the XML output into a plain string.
Grouped Row Aggregation with FOR XML PATH
To group values by a column, such as product categories, you can use a correlated subquery within the SELECT clause. This technique produces one row per group with a comma-separated list of related values:
sql
CopyEdit
SELECT p.Category,
STUFF((
SELECT ‘, ‘ + p2.ProductName
FROM Products p2
WHERE p2.Category = p.Category
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) AS ProductList
FROM Products p
GROUP BY p.Category;
This query uses the alias p2 in the subquery to select all products in the same category as the outer query row (p.Category). The result is one row per category with the products listed in a single comma-separated string.
Example Output
nginx
CopyEdit
Category ProductList
Fruits Apple, Banana, Mango
Vegetables Carrot, Spinach, Cabbage
Aggregating All Rows Without Grouping
If you want to create a single comma-separated list of all product names without grouping, the query becomes much simpler:
sql
CopyEdit
SELECT STUFF((
SELECT ‘, ‘ + ProductName
FROM Products
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) AS CSV_Result;
This version outputs:
nginx
CopyEdit
CSV_Result
Apple, Banana, Mango, Carrot, Spinach, Cabbage
Benefits of Using FOR XML PATH
This method is widely supported in all SQL Server versions from 2005 onward, making it highly valuable for legacy systems. It provides flexibility through the use of correlated subqueries and can be used with or without grouping. It also offers reliable handling of special characters when the TYPE directive is included, ensuring safer output that avoids XML encoding issues.
Limitations of FOR XML PATH
The FOR XML PATH method, while powerful, has its drawbacks. It is more complex than STRING_AGG() and requires additional handling such as the STUFF() function to clean up the output. It can be less readable for beginners and may become cumbersome in complex queries. The XML conversion process can also impact performance when working with large datasets. Additionally, since it relies on string concatenation, it is limited by the maximum size of VARCHAR(MAX) or NVARCHAR(MAX) fields. Special characters like & or < will be escaped in the XML unless the .value(‘.’, ‘NVARCHAR(MAX)’) syntax is correctly applied.
Using STRING_ESCAPE and COALESCE for Custom CSV Aggregation in SQL Server
While STRING_AGG() and FOR XML PATH are the most common solutions for converting multiple rows into comma-separated values, there are additional techniques that can enhance or replicate this functionality in specific scenarios. In this section, we explore how to use STRING_ESCAPE to sanitize string output and how to implement manual row aggregation using COALESCE, especially in environments where neither STRING_AGG() nor XML support is available. We also look at error handling and performance considerations to ensure your queries remain stable and efficient.
Sanitizing Output with STRING_ESCAPE
When converting multiple rows to a single string, it is important to consider the possibility of special characters that may break formatting or interfere with downstream processing. Characters like quotes, commas, or newline characters can cause issues when the output is intended for use in JSON, HTML, or other structured formats. SQL Server provides the STRING_ESCAPE() function to help with this.
Purpose of STRING_ESCAPE
The STRING_ESCAPE() function returns a string with special characters escaped using a specified format. This is particularly useful when building comma-separated strings for export or display. The function currently supports only the JSON format, which escapes characters like quotes and backslashes.
Example Usage
If a product name contains special characters, you can use STRING_ESCAPE() inside your aggregation logic to ensure safe output. This is especially relevant when preparing data for JSON APIs.
sql
CopyEdit
SELECT STRING_AGG(STRING_ESCAPE(ProductName, ‘json’), ‘, ‘) AS EscapedList
FROM Products;
This version ensures that any embedded quotes or special characters within the ProductName field are escaped properly. The result is a clean and valid string that can safely be included in a JSON response or similar context.
Limitations of STRING_ESCAPE
The function only supports the ‘json’ format. If you require escaping for HTML, XML, or CSV-specific characters, you will need to write custom logic or handle it at the application level. Additionally, STRING_ESCAPE() is supported only in SQL Server 2016 and later.
Using COALESCE to Simulate Row-to-CSV Aggregation
In older versions of SQL Server that do not support STRING_AGG() or reliable XML output, you can simulate row aggregation manually using a COALESCE() pattern. This approach involves iteratively building a string inside a query by appending values from each row.
Manual Aggregation with COALESCE
You can use a variable to hold the result and build the string within a SELECT query. Here is a simple example:
sql
CopyEdit
DECLARE @CSV NVARCHAR(MAX) = ”;
SELECT @CSV = COALESCE(@CSV + ‘, ‘, ”) + ProductName
FROM Products;
SELECT @CSV AS CSV_Result;
This query loops through all product names and concatenates them into a single comma-separated string. The COALESCE() function ensures that no leading comma appears on the first iteration.
Using COALESCE with Grouping
To group rows by a category and concatenate product names manually, a more complex solution is required. It typically involves a cursor or a user-defined function, which can be less efficient and harder to maintain than modern alternatives. Due to the verbosity of that method, it is recommended only when other options are unavailable.
Performance and Compatibility
The COALESCE() method works in all versions of SQL Server and does not require XML support. However, it is not efficient for large datasets and does not support grouping out-of-the-box. It is best used for small data volumes or within stored procedures where more control is required over execution logic.
Error Handling and Output Limits
When working with string aggregation in SQL Server, it is important to be aware of certain limits and potential errors.
Maximum String Size
The output of string aggregation is limited to the maximum size of VARCHAR(MAX) or NVARCHAR(MAX), which is 2^31-1 characters. If your aggregation exceeds this size, you may receive an error or experience silent truncation depending on how the result is used. Always use NVARCHAR(MAX) or VARCHAR(MAX) in your queries to ensure that large results are handled correctly.
Handling NULL Values
If the input values being concatenated include NULL, the behavior depends on the method used. In STRING_AGG(), NULL values are ignored by default. In manual aggregation with COALESCE(), they may result in unexpected output unless explicitly handled. You can filter out NULLs or use ISNULL() or COALESCE() to substitute them with empty strings before concatenation.
sql
CopyEdit
SELECT STRING_AGG(ISNULL(ProductName, ”), ‘, ‘) FROM Products;
This ensures that any NULL values are converted to empty strings and do not interrupt the concatenation.
Conclusion: Best Practices for Converting Multiple Rows to a Comma-Separated Value in SQL Server
Transforming multiple rows into a single comma-separated value is a practical requirement in many SQL Server use cases. Whether you are formatting data for reports, exporting to external systems, or creating readable summaries, this capability enhances both the usability and flexibility of SQL output. Depending on the SQL Server version and specific needs, there are multiple techniques available to achieve this transformation.
Choosing the Right Method Based on SQL Server Version
Selecting the appropriate method for converting multiple rows into a comma-separated value in SQL Server depends largely on the version of SQL Server you are using and the specific requirements of your application. Each technique—whether STRING_AGG(), FOR XML PATH, or COALESCE()—has strengths and limitations that affect usability, performance, maintainability, and compatibility. Understanding when and how to use each method can help you write more robust SQL code and make informed architectural decisions.
STRING_AGG() in SQL Server 2025 and Later
The STRING_AGG() function was introduced in SQL Server 2025 and quickly became the preferred method for aggregating string values from multiple rows. It provides a clear and concise syntax, high performance, and excellent integration with other SQL features such as GROUP BY, window functions, and common table expressions (CTEs).
With STRING_AGG(), the process of transforming a set of row values into a delimited string becomes as straightforward as a single-line query. This function accepts a column and a delimiter as arguments and returns a single concatenated string for all rows in a group.
sql
CopyEdit
SELECT Category, STRING_AGG(ProductName, ‘, ‘) AS ProductList
FROM Products
GROUP BY Category;
This simplicity not only enhances readability but also reduces the likelihood of bugs compared to more complex XML or string-manipulation techniques. The function gracefully skips NULL values, ensuring cleaner output without needing additional filtering logic.
In modern SQL Server environments, especially those with complex reporting or data transformation requirements, STRING_AGG() fits well. It also works effectively in subqueries and CTEs, which are frequently used in modern ETL workflows and application-level data shaping.
Moreover, STRING_AGG() is optimized for large datasets and performs efficiently with proper indexing and memory management. When dealing with millions of rows, it can handle aggregation faster than legacy workarounds, especially when parallel query execution is enabled.
However, despite its many benefits, STRING_AGG() has some limitations. It does not allow conditional delimiters or dynamic grouping logic beyond standard SQL clauses. Also, it may raise errors or truncate results if the output exceeds the allowed size for VARCHAR(MAX) or NVARCHAR(MAX). Special care must be taken to test performance under maximum load if the expected result strings are particularly long.
FOR XML PATH for SQL Server 2005 to 2016
For environments where SQL Server versions earlier than 2025 are still in use, FOR XML PATH remains a reliable and flexible solution for string aggregation. This method was originally designed for XML data formatting but has long been repurposed by developers to build delimited strings from multiple rows.
The core idea behind this technique is to render rows into XML fragments using FOR XML PATH(”), then collapse those fragments into a single string using STUFF() to remove the unwanted leading delimiter.
sql
CopyEdit
SELECT Category,
STUFF((
SELECT ‘, ‘ + ProductName
FROM Products p2
WHERE p2.Category = p1.Category
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) AS ProductList
FROM Products p1
GROUP BY Category;
This method works in all editions of SQL Server from 2005 onward, making it the best option for legacy applications that cannot be upgraded. It supports grouped aggregation, conditional concatenation using WHERE clauses, and can be extended to nested queries when needed.
One of the advantages of FOR XML PATH is its ability to output well-formed strings even in the presence of special characters. By using the .value(‘.’, ‘NVARCHAR(MAX)’) syntax, it escapes and then correctly decodes XML entities, preventing malformed strings when data includes characters like &, <, or >.
Despite its flexibility, FOR XML PATH is more verbose and can be difficult to read or maintain, especially for developers unfamiliar with XML-based workarounds. It may also perform slower than STRING_AGG() on large datasets because the XML rendering process introduces overhead, especially if many joins or correlated subqueries are involved.
Another concern is potential encoding issues. If the .value() function is omitted or misused, the result string may include raw XML-escaped characters. This can be particularly problematic in display layers or exported reports.
Yet, in stable systems where SQL Server upgrades are not feasible, this method continues to be a practical and dependable tool.
COALESCE for Manual String Aggregation
In the rare situations where neither STRING_AGG() nor XML functionality is available or allowed—such as in strict environments, sandboxed SQL instances, or stored procedures with execution restrictions—the COALESCE() method offers a manual approach to row aggregation. This pattern involves assigning a variable to accumulate the result string iteratively, one row at a time.
sql
CopyEdit
DECLARE @CSV NVARCHAR(MAX) = ”;
SELECT @CSV = COALESCE(@CSV + ‘, ‘, ”) + ProductName
FROM Products;
SELECT @CSV AS ProductList;
Although this method works in all SQL Server versions, including very old or restricted environments, it has significant limitations. It does not natively support grouping, so creating separate lists per category or any other grouping condition requires procedural logic such as cursors or loops.
The COALESCE() method is not set-based, which violates one of the core performance principles in SQL. Since it processes rows sequentially and stores intermediate results in a scalar variable, it can become a performance bottleneck on even moderately sized tables.
However, its simplicity can be advantageous in certain contexts. For example, in stored procedures where the result must be built into a variable for dynamic SQL or returned as a scalar value, COALESCE() provides a lightweight and easily controlled method. It also avoids the complexities of XML syntax, making it suitable for developers with minimal experience in advanced SQL features.
To improve the robustness of this approach, it is essential to manage NULLs proactively and test the result size to avoid truncation. Since this method uses variables, the size limit is governed by the data type. Always use NVARCHAR(MAX) or VARCHAR(MAX) to avoid clipping of long strings.
Performance and Maintainability Considerations
When choosing between these methods, performance and maintainability should always be part of the decision-making process. While STRING_AGG() is the most performant and readable, it is not always an option. FOR XML PATH balances legacy support and advanced capability but is harder to maintain. COALESCE() is broadly compatible but limited in flexibility and efficiency.
In production environments, string aggregation can be a major factor in query performance. For example, repeated XML operations in correlated subqueries can quickly degrade performance if not optimized with proper indexes and filtering. Conversely, STRING_AGG() can benefit from parallelism and memory grants, especially in enterprise editions.
Maintenance also plays a key role. A STRING_AGG() query is easier to troubleshoot and extend than one involving STUFF() and nested XML. Developer teams working across multiple environments should document these queries thoroughly to avoid future confusion.
the optimal method for converting rows to comma-separated values depends on your SQL Server version, system architecture, and performance requirements. Use STRING_AGG() wherever possible for its simplicity, efficiency, and compatibility with modern SQL development. Rely on FOR XML PATH for legacy systems where backward compatibility is essential. Reserve COALESCE() for edge cases, minimal workloads, or procedural SQL where other options are unavailable. Understanding the strengths and weaknesses of each method empowers you to write efficient, maintainable, and version-aware SQL code for all data aggregation scenarios.
Handling Special Characters and Output Quality
In scenarios where the output may contain special characters or is intended for formats like JSON, the STRING_ESCAPE() function provides a safeguard by escaping characters such as quotes and backslashes. This function helps ensure that your output is safe for use in APIs, files, or web-based systems. However, it is only applicable when JSON escaping is appropriate.
Always consider the size of the output string and the potential presence of NULL values in your data. Use ISNULL() or COALESCE() to manage NULLs and avoid unexpected gaps in your results. Keep in mind that all aggregation methods are subject to maximum string length limits, and performance may degrade with very large data sets.
Final Thoughts
Choosing the best method for converting rows to comma-separated values in SQL Server depends on your environment, SQL Server version, and specific requirements for performance, compatibility, and formatting. By understanding and applying the right technique, you can produce clean, efficient, and readable results suitable for a wide range of real-world applications. Whether you are writing queries for modern systems or maintaining legacy databases, these methods give you the tools needed to handle string aggregation reliably and effectively.