Concatenate a selected column in a single query?

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!

From stackoverflow

• 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
  

  Jagd : Reusability! I like it. :)

• 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

• Use coalesce. Something like this:

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

  Jagd : Is there any reason to use COALESCE over just a normal "select @str = @str + ',' + FirstName + ' ' + LastName"?? I did some reading and the only added benefit that I could find is that COALESCE will ignore null entries.

  Paul : Yes there is, without coalesce you will end up with an extra comma at the beginning of @str.

  Jagd : Actually, you end up with a leading comma using COALESCE too, unless you wrap the @Names variable with the IsNull function. COALESCE(IsNull(@Names + ', '), '').

  Jagd : Oops... lets try that again: coalesce(IsNull(@Names+ ', ', ''), '')

  Paul : In SQL 2005 the code above gives me no leading or trailing commas.

  Paul : null + 'string' = null, so if the @str is null (on the first pass) the second string, '', is used. The nested ISNULL is just going to duplicate the coalesce, because it will ALWAYS use the first value. (@str +', ' or '')

  Jagd : Ah, you are correct. I was initializing my @Names to an empty string. Initialing it to null did the trick.

• 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

• 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
  

• 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.

  John Gibb : It looks like someone mentioned this approach in one of the links above...