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
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.