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:

clip_image001

Advertisements

2 thoughts on “Concatenate a row set into a sql column

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