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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.