Concatenate a selected column in a single query?

Question

I know you can do this, because I've seen it done once before, but I forget where and up until now I haven't need to do it.

I have a table called Employees, and it has various employee data (duh). I need a query that will do a select on the first and last name of all rows in the table, and then contenate all of them into a comma delimited string.

For example, I have a few rows (a lot more than that actually, but for the sake of this question just assume two) of data that look like:

FName    LName
-------  -----
Richard  Prescott
Jill     Sentieri
Carol    Winger

I need to a select that can return the aforementioned data in this form:

Richard Prescott, Jill Sentieri, Carol Winger

Thank you in advance for your help!

Answer 1

you can write a UDF to do that

CREATE FUNCTION [dbo].[fnc_GetEmpList](
@CompId numeric
) RETURNS nvarchar(1000)
BEGIN

declare @str nvarchar(1000)
set @str =''

select  @str = @str + ',' + FirstName + ' ' + LastName from Employees


--remove the last comma
if(@str<>'')
    set @str = right(@str,len(@str)-1)

return @str


END

Answer 2

Maybe this can help. This question has been asked before. http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery

Answer 3

Use coalesce. Something like this:

DECLARE @Names varchar(1000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM Employees

Answer 4

If you are using MySQL, they have a great function called GROUP_CONCAT that does just that. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Answer 5

this isn't perfect, but it'll get you most of the way there

declare @count int
declare @i int
declare @string nvarchar(max)
declare @name nvarchar(100)

declare @Employees (EmpName nvarchar(100), ID int identity(1,1)

insert into @Employees (EmpName)
select FirstName + ' ' + LastName
from Employees


select @count=count(*) from @Employees
set @i=1
set @string=''


while (@i<=@count)
begin

    select @name = EmpName from @Employees where ID=@i

    set @string = @string + ',' + @name

    set @i=@i+1
end

Answer 6

This is the most efficient method I've found. It requires SQL Server, but it sounds like that's what you're using.

select stuff((
    select ', ' + fName + ' ' + lName
    from Employees
    order by lName, fName /* Optional */
    for xml path('')
), 1, 2, '');

The idea is that you can take advantage of the ability to use an empty tag name with for xml path('') to get string concatenation across rows. The stuff(...,1,2,'') just removes the leading comma.

This is REALLY fast.