sql - 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

Use coalesce. Something like this:

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