Скачать или смотреть ⚡ SQL One-Liner: Convert Column Values to a Comma-Separated List Instantly!

Unlock the power of SQL string aggregation with this essential one-liner that converts column values into a comma-separated list. This technique is incredibly useful for generating summaries, reports, or simply presenting a list of names or other values in a single, concise string output.

Traditional Approach Using FOR XML PATH and STUFF:

FOR XML PATH(''): This method was widely used before the introduction of native string aggregation functions. It concatenates values from a column into a single XML-formatted string. However, because each value is prefixed with a comma, the result has an extra leading comma.

STUFF Function: To correct this, the STUFF function is used to remove the first character (i.e., the extra comma) from the resulting string, providing a clean output.
This method, although effective, involves a workaround that can seem complex at first glance.

Shortcut Using STRING_AGG:

With SQL Server 2017 and later, Microsoft introduced the STRING_AGG function, a native way to perform string aggregation. With just one simple function call, you can concatenate column values with a specified delimiter—in our case, a comma.

Benefits:

Simplicity: The query is compact and straightforward, eliminating the need for XML processing and the additional STUFF function.

Readability and Performance: The one-liner is much easier to read and maintain, and it typically performs better on large datasets since it is optimized for string aggregation.

This trending method is highly searched by database professionals and data analysts looking to simplify their code and improve query performance. Mastering these techniques will boost your data reporting capabilities and help you generate meaningful summaries with minimal code.

💡 Pro Tip:

Always consider the version of your SQL Server environment. If you’re on SQL Server 2017 or later, prefer STRING_AGG for its elegance and efficiency. For earlier versions, the FOR XML PATH approach remains a reliable solution.

Queries:

✅ Long Way (Using FOR XML PATH and STUFF in SQL Server):

SELECT STUFF(
(SELECT ',' + Name
FROM Employees
FOR XML PATH('')), 1, 1, ''
) AS CommaSeparatedNames;

Explanation:

FOR XML PATH('') concatenates the values of the Name column into a single XML string with a comma prefix for each value.

STUFF(..., 1, 1, '') removes the leading comma from the concatenated string.

This method builds the comma-separated list manually using XML functions.

✅ Shortcut One-Liner (Using STRING_AGG in SQL Server 2017+):

SELECT STRING_AGG(Name, ',') AS CommaSeparatedNames
FROM Employees;