Return result set as string

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *