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:
Table called tblDogs:
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:
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...