Concatenate a list into one string separated by commas 1

[shaleen7]

Table Data
Index FirstName LastName
1 Doug Reid
2 Laura Rie
3 Jerry Jones
4 Carl Lind
5 Kelly Ham

I'm trying to write a SQL statement where I list of names in a table will be converted so that each new name is separated by a comma.

Desired Results:
Doug Reid, Laura Rie, Jerry Jones, Carl Lind, KellyHam

FirstName(tablename) LastName(tablename)
from tablename

Any suggestions. Does it have to be a procedure?

 

[WurzelGummidge]

Hi Shaleen,
Try this:

select Firstname + ' ' + Lastname + ',' as FullName from ........

 

[swampBoogie]

declare @result varchar(8000)
set @result = null

select @result = coalesce(@return+',','') + 
   firstName + LastName 
from t
order by id
select @result

 

[maswien]

Table Data
Index FirstName LastName
1 Doug Reid
2 Laura Rie
3 Jerry Jones
4 Carl Lind
5 Kelly Ham


declare @result as varchar(1024)
declare @fname as varchar(50)
declare @lname as varchar(50)
select @result=''
DECLARE concate_cursor CURSOR FOR
SELECT FirstName, LastName
FROM mytable
OPEN concate_cursor
FETCH NEXT FROM concate_cursor into @fname, lname
WHILE @@FETCH_STATUS = 0
BEGIN
select @result= @result + @fanme + ' ' + @lname + ', '
FETCH NEXT FROM concate_cursor into @fname, lname
END
CLOSE authorcursor
DEALLOCATE authorcursor
select @result

 

[maswien]

Sorry for the typos in last comment, the SQL should be:




declare @result as varchar(1024)
declare @fname as varchar(50)
declare @lname as varchar(50)
select @result=''
DECLARE concate_cursor CURSOR FOR
SELECT FirstName, LastName
FROM testconcate
OPEN concate_cursor
FETCH NEXT FROM concate_cursor into @fname, @lname
WHILE @@FETCH_STATUS = 0
BEGIN
select @result= @result + @fname + ' ' + @lname + ', '
FETCH NEXT FROM concate_cursor into @fname, @lname
END
CLOSE concate_cursor
DEALLOCATE concate_cursor
select @result

 

[JamesLean]

swampBoogie's solution is far more efficient than using a cursor. Just fix the typo and a bit of formatting:

DECLARE @names varchar(8000)

SELECT @names = ISNULL(@names + ', ', '') + ISNULL(firstname, '') + ' ' + ISNULL(lastname, '')
FROM t

SELECT @names AS names

--James