I recently had to create a string of values from a multi table join in sql and I wasn’t sure how to accomplish this so I did some research and came across the SUBSTRING function in SQL server. This function takes a select statement and then the length to begin with and the max length of the string you want to return. I used 1000 just to be safe but my string wouldn’t ever be larger than that. I also used the FOR XML to return the data as xml and pass that to the SUBSTRING fuction for parsing. I have posted the code below, enjoy.
select SUBSTRING((
SELECT (',' + Table1.Name)
FROM Table1 join
Table2 on Table2.SomeID = Table1.SomeID
FOR XML PATH('')
), 2, 1000)
