SQL Server query returning comma separated values

Question

Possible Duplicate:
Combine multiple results in a subquery into a single comma-separated value

I have a query that returns a person object and needs to also return an organisations column which contains a comma separated list of organisations. My current query is below:

SELECT
p.PersonID,
p.Title,
p.FirstName,
p.LastName,
p.DateOfBirth,
p.EmailAddress,
p.MobileNumber,
p.EntityID,
(SELECT o.Name FROM Entities.Organisations o
    JOIN Entities.OrganisationPeople op ON o.OrganisationID = op.OrganisationID        AND op.PersonID = p.PersonID) AS 'Organisations'
  FROM
Entities.People p

This is fine when there is one organisation attached to a person, but I might as well just do a standard join. I want to return possibly more than one result in the organisations column with a comma separated list.

Is this even possible?

There are also some ideas at http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Brian Hoover, Nov 11 '11 at 14:57
And http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column — OMG Ponies, Nov 11 '11 at 15:02

score 0 · Answer 1 · edited May 23 '17 at 12:29

From here, here's an example:

1. Create the UDF

CREATE FUNCTION CombineValues
(
@F_ID INT --The foreign key from TableA which is used to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);

SELECT @SomeColumnList = COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM TableB C
WHERE C.FK_ID = @FK_ID;

RETURN 
(
SELECT @SomeColumnList
)
END

2. Use in subquery

SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA

SQL Server query returning comma separated values

1 Answers1