Search This Blog

Tuesday, June 15, 2010

Concatenating row values into one grouped column

Sometimes, small things create alot of problems. Like, a core issue discussed in here.
Consider we have a table as;

Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif')
Insert into @vTable Values (1,'Sheikh')
Insert into @vTable Values (2,'Asif')
Insert into @vTable Values (2,'Sheikh')
Insert into @vTable Values (2,'Sheikh2')
Insert into @vTable Values (2,'Sheikh3')

 
Now we need to concatenate values as;
 
ID      Vals
1       Atif Sheikh
2       Asif Sheikh Sheikh2 Sheikh3
 
 
There may be alot of ways to perform this but using XML Path here is a savior. The query is;
 
Select Distinct id,
Stuff((Select ' ' + [val] from @vTable t2 where id = Main.ID for XML Path('')),1,1,'') as Vals
from @vTable Main

1 comment:

  1. I am actually using MS SQL 2000 and FOR XML PATH is not available but FOR XML AUTO, FOR XML EXPLICIT, FOR XML RAW is available.

    Which among those suites best for substituting FOR XML PATH?

    ReplyDelete