How to Concatenate SQL Query Results into a Single String in SQL Server?

Concatenating SQL Query Results into a Single String

In SQL Server, the task of combining multiple query results into a single string can be achieved using various techniques.

FOR XML PATH and STUFF

For SQL Server 2005 and above, the FOR XML PATH and STUFF combination offers a straightforward approach:

DECLARE @CodeNameString varchar(100)

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

This method concatenates the query results into a single XML string and then uses STUFF to remove the leading "," character.

XML-based Concatenation

Another option is to leverage XML concatenation functionality:

DECLARE @CodeNameString varchar(100)

SET @CodeNameString = (SELECT CodeName FROM dbo.AccountCodes FOR XML PATH(''))

Cursor-based Approach

In cases where XML concatenation is not feasible, a cursor-based approach can be employed:

DECLARE @CodeNameString varchar(100)
DECLARE @CodeName varchar(50)

DECLARE cursor CURSOR FOR
SELECT CodeName
FROM dbo.AccountCodes
ORDER BY Sort

OPEN CURSOR
FETCH NEXT FROM CURSOR INTO @CodeName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @CodeNameString = @CodeNameString + @CodeName + ','
   FETCH NEXT FROM CURSOR INTO @CodeName
END

CLOSE CURSOR
DEALLOCATE CURSOR

This method iterates through the query results and appends them to the @CodeNameString variable.

The Preferred Approach

The preferred approach depends on the SQL Server version and specific data characteristics. For cases where the data contains special characters like '<', '>' or '&', consider using the FOR XML PATH and STUFF technique, while for larger datasets or performance-sensitive scenarios, the cursor-based approach may be more appropriate.

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