Archive for the ‘SQL’ category

Create Comma delimited string from multiple tables in SQL

June 1st, 2012

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)