How to Efficiently Concatenate SQL Query Results into a Single String?

Accumulating SQL Query Results into a Single String

Concatenating query results into a single string is a common task in database programming. In SQL Server, there are two approaches to achieve this: using the FOR XML PATH & STUFF technique or leveraging the STRING_AGG function.

FOR XML PATH & STUFF

For SQL Server versions 2005 and above, the FOR XML PATH & STUFF combination is an effective approach:

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

This solution concatenates the results using FOR XML PATH('') into an XML string, then uses STUFF to remove the initial comma inherited from the XML concatenation.

STRING_AGG Function

In SQL Server 2017 and later, the STRING_AGG function provides a dedicated solution for this task:

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STRING_AGG(CodeName, ',')
FROM 
   dbo.AccountCodes
ORDER BY 
   Sort

The STRING_AGG function concatenates the results directly into a single string, separated by the specified delimiter (in this case, ',').

Handling Special Characters

It's important to note that the FOR XML PATH & STUFF approach may encode certain special characters, such as '<', '>', and '&', into XML entities (<, >, and &). For situations where these characters are problematic, the STRING_AGG function with the USING XMLTYPE clause can be used:

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STRING_AGG(CodeName, ',') USING XMLTYPE
FROM 
   dbo.AccountCodes
ORDER BY 
   Sort

The above is the detailed content of How to Efficiently Concatenate SQL Query Results into a Single String?. For more information, please follow other related articles on the PHP Chinese website!