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

Which RDBMS are you using? Also, what is your reason for wanting to do this? It may be something that is better handled on the front-end of your application. — Tom H, Apr 30 '09 at 19:29

score 8 · Accepted Answer · answered Apr 30 '09 at 19:29

8

Use coalesce. Something like this:

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

answered Apr 30 '09 at 19:29

Paul

6,188
1
41
63

1

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. – Jagd Apr 30 '09 at 20:18
Yes there is, without coalesce you will end up with an extra comma at the beginning of @str. – Paul May 01 '09 at 12:37
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 May 01 '09 at 16:25
Oops... lets try that again: coalesce(IsNull(@Names+ ', ', ''), '') – Jagd May 01 '09 at 16:25
In SQL 2005 the code above gives me no leading or trailing commas. – Paul May 01 '09 at 16:36
1

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 '') – Paul May 01 '09 at 16:44
Ah, you are correct. I was initializing my @Names to an empty string. Initialing it to null did the trick. – Jagd May 01 '09 at 18:11

score 2 · Answer 2 · answered Apr 30 '09 at 22:22

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.

score 1 · Answer 3 · answered Apr 30 '09 at 19:27

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

score 0 · Answer 4 · answered Apr 30 '09 at 19:35

0

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

answered Apr 30 '09 at 19:35

Ben Hayden

1,349
9
15

score 0 · Answer 5 · answered Apr 30 '09 at 19:36

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

Concatenate a selected column in a single query?

5 Answers5

Linked

Related