Concatenate rows into a column separated by a delimiter


Howdy EveryOne

While working on the MSDN Forums for quite a while, I see every now and then  people wants to know how to concatenate a single column value of multiple rows into a single column…. I think it might be better understood by a example

Table A: INPUT

Col1
Row 1 a
Row 2 b
Row 3 c
Row 4 d

Desired output:

a,b,c,d

If this is the case then without using the XML tags previously we used to do the below one

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
declare @concate varchar(100)
set @concate = ''
SELECT @concate = @concate +','+ a
FROM @t
select STUFF(@concate,1,1,'')

Although it is a good approach, The select statement (concate = concate+…) needs to be executed in a separate batch. This approach will need an extra lines of code incase if this concatenated value is needed else where….

So, in search of a better solution, I stumbled upon a post written by Kent W. in MSDN forums with FOR XML PATH which will accomplish my task very smoothly.

Below is an example of how to write the above code with the FOR XML PATH

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
SELECT STUFF( (SELECT ',' + a FROM @t FOR XML PATH ('')),1,1,'')

Advantages of the second approach:
No need of additional variables.
Can be included in a subquery directly.

Advertisements

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