How to turn a list of (e.g.) countries into a concatenated string (e.g. France, New Zealand, California)
There’s a comprehensive post about different methods at https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485 : the method I’ve used is “Scalar UDF with t-SQL update extension”
ALTER function [dbo].[GetCountriesOfOrigin](@productId int) returns varchar(max) as begin declare @result varchar(max) set @result = '' select @result = @result + c.CountryOfOrigin + ', ' from tblProductCountry pc inner join tblCountryOfOrigin c on pc.CountryOfOriginID=c.CountryOfOriginID where pc.ProductID=@productId order by c.CountryOfOrigin set @result = ltrim(rtrim(@result)) if right(@result,1)=',' set @result=left(@result, len(@result)-1) --damn LEN! ignores trailing spaces if len(@result)=0 set @result=null return @result end