I have a table with
SELECT fName, lName FROM person
It returns 3 results
I want to return a concat(fName, ‘ ‘, lName) and 2 “and”s in between each concat.
Jon Snow and Margaery Tyrell and Peter Griffin
But I also want it to be dynamic to scale if the result grows. So if it is 6 it will have the names concat with 5 “and”s.
I am trying to output this to a VARCHAR variable.
Using MS SQL Server 2014.
This should get you going – basically what irimias did at this link
Declare @Table table (FirstName varchar(100), LastName varchar(100)) DECLARE @ConcatString varchar(100) insert into @Table (FirstName,LastName) values('TomFirst','TomLast'), ('DickFirst','DickLast'), ('HarryFirst','HarryLast') SELECT @ConcatString = isnull(@ConcatString + ' AND ', '') + FirstName + ' ' + LastName from @Table print @ConcatString