CODEWITHSUNDEEP
sqlsql-serverselectconcatenation
Jagd
Jagd

Reputation: 7306

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

Upvotes: 1

Views: 6379

Answers (5)

John Gibb
John Gibb

Reputation: 10773

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.

Upvotes: 2

DForck42
DForck42

Reputation: 20327

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

Upvotes: 0

Ben Hayden
Ben Hayden

Reputation: 1379

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

Upvotes: 0

Paul
Paul

Reputation: 6218

Use coalesce. Something like this:

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

Upvotes: 8

Vikram
Vikram

Reputation: 6877

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

Upvotes: 1

Related Questions