Something we need to convert a table into delimited string for, say, log or exception message, there are three ways to do it in TSQL
Aggregate with variable
1
2
3
4
5
| DECLARE @Result NVARCHAR(2000) = N'' /*Important! Assign empty string to the return variable*/
SELECT @Result = @Result + CONVERT(NVARCHAR(100), ID) + N','
FROM @Table
SET @Result = SUBSTRING(@Result, 0, LEN(@Result)) /* Need to remove trailing delimiter*/
|
Use XML PATH
1
2
3
| DECLARE @Result NVARCHAR(2000) /* Initial assignment is optional */
SELECT @Result = STUFF((SELECT ',' + CONVERT(NVARCHAR(100), ID) + ',' + Description FROM @Result FOR XML PATH('')), 1, 1, '' )
|
Use COALESCE
1
2
3
| DECLARE @result NVARCHAR(1000) = N''
SELECT @result = COALESCE(@result + ',', '') + description FROM targetingservice
|