Concatenate a row set into a sql column

Other day I made a post on how to concatenate a rowset into a TSQL variable: here

In this post I’ll show how to make a concat into a column without using any variable:

select (

select Col + ', '

from ( select 'Value 1' Col union select 'Value 2' union select 'Value 3' ) aux FOR XML PATH('')

) ConcatColumn

The trick is on "FOR XML PATH("")" that transform the rows into a xml string where the node name is passed by parameter, in this case I pass an empty string, so the result is:

Concatenate a row set into a string with one statement in TSQL

Imagine a SQL statement like this:

select 'a' Col

union

select 'b'

union

select 'c'

union

select 'd'

If you want to concatenate all the rows into a string one possible solution is to declare a cursor and make the concatenation.

But you can also do it like this:

declare @concatStr nvarchar(max)

SELECT @concatStr = isnull(@concatStr + ', ','') + aux.Col

FROM (
select 'a' Col

union

select 'b'

union

select 'c'

union

select 'd'

) aux

print @concatStr

Result:

“a, b, c, d”