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”

Advertisements

One thought on “Concatenate a row set into a string with one statement in TSQL

  1. Pingback: Concatenate a row set into a sql column « Rui Romano Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s