Use COALESCE to put a column of data into a delimited list variable

Here’s an example of how to create a delimited list, using a field of rows of data and COALESCE.

CREATE TABLE #tmp (ID INT IDENTITY, Value VARCHAR(10))

INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)

DECLARE @vcList VARCHAR(100)
SELECT @vcList = COALESCE(@vcList + ‘;’, ”)
+ ISNULL(Value, ‘0’)
FROM #tmp

-- ORDER BY might not work
PRINT @vcList
Resulting in:
10;1;2;3;4;5;6;7;8;9

NOTE:
Don’t be surprised when ORDER BY doesn’t work.

‘SQL Server query processor builds an different execution plan when expressions are applied to columns in the query’s ORDER BY clause, than when those same expressions are applied to columns in the query’s SELECT list. The decision made by the query processor is based on the cost of possible execution plans.’