Concatenate many rows into one single text string (SQL)

I recently starting re-coding some of my old Visual Basic 6 code to make it faster and more efficient while adding it to VB.Net and taking out many of the steps.  In this process, I have taken many lines of code and let SQL do most of the work for me by being smarter with my SQL Statements I produce.  One huge time saver I ran across was this one that I am going to show you.


I have three tables (tblOwner, tblDogs, EndTable) that I am working with.  The field Dog is found common in all three tables (I made typed it in Red Font).  I have a field called Owner that I wish to add together while being separated with commas without having to write code to loop and edit from VB.  Luckily I ran across a couple of articles that explained it to a degree.  I was able to join two tables with a one-to-many relationships.  In SQL, I found that XML Path method can do the concatenation of the rows very easily.


Table called tblOwner:

Dog             Owner
----------      -------------
13               Mark
13               Jerry
13               Paul
44               John
44               Duke

Table called tblDogs:

Dog             DogName
----------      -------------
13               Fido
44               Snoopy

Here is my code:

Update EndTable 
set Owner = t.Owners 
              from (Select distinct ST2.dog,  
          substring( 
          (   Select  ', '+  Owner  AS [text()] 
              From  tblDogs ST1 
              join tblOwners on st1.dog = tblOwners.dog 
              Where ST1.dog = ST2.dog               
              ORDER BY ST1.dog 
              For XML PATH ('') 
           ), 2, 1000) [Owners] 
From dbo.EndTable ST2)  t  
join EndTable 
on EndTable.dog = t.dog 
where t.dog > 0

Result that are expected for EndTable:

Dog             DogName              Owners
----------      -------------        -------------
13               Fido                 Mark, Jerry, Paul
44               Snoopy               John, Duke

I hope this helps someone out a bit and saves them a bit of time.  I know it helped me out greatly.


If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...