How to Concatenate SQL Query Results into a Single String?

Concatenating SQL Query Results into a Single String

Problem:

In SQL, a function includes a query that retrieves a list of CodeName values from the AccountCodes table ordered by Sort. The goal is to concatenate these values into a single string and store the result in a CodeNameString variable.

Solution:

Using FOR XML PATH and STUFF (SQL Server 2005 or later):

DECLARE @CodeNameString varchar(100)

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

This method uses the FOR XML PATH clause to concatenate the CodeName values into an XML string. The resulting XML is then converted back to a string using the STUFF function, with the first character (a comma) being removed.

Note: This approach may not work if the CodeName values contain specific characters like <, >, or &. In such cases, an alternative solution is recommended, such as the one proposed below:

Alternative Solution (Using DISTINCT and FOR XML RAW):

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = DISTINCT(CodeName) 
WHERE 
   EXISTS (SELECT 1 FROM dbo.AccountCodes WHERE CodeName = @CodeNameString)
FOR XML RAW, ',')

This method uses the FOR XML RAW clause to create an XML string containing the concatenated values, excluding duplicates. The string is then assigned to the CodeNameString variable.

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