Ray Jezek’s Blog

I will forever be grateful to Ryan for telling me about how to create comma delimited strings out of many-to-many relationships in SQL Server. It’s an absolute gem!
So if were trying to bring back Employees and their territories from Northwind you could do the following:
CREATE FUNCTION dbo.GetCommaList(@EmployeeID int)
RETURNS varchar(500) AS
BEGIN
DECLARE @StringList varchar(500)
SELECT @StringList = COALESCE(@StringList + ‘, ‘, ”) + RTRIM(t.TerritoryDescription)
FROM EmployeeTerritories et
INNER JOIN Employees e ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID
WHERE et.EmployeeID = @EmployeeID

IF @StringList IS NULL
SET @StringList = ‘No Territories’
RETURN @StringList
END
And then:
SELECT FirstName, LastName, dbo.GetCommaList(EmployeeID) AS Territories
FROM Employees
This would return the list of employees with all of their corresponding territories in a comma delimited string. Of course there are a number of variations of this same idea that you could so but the prinicple is useful in so many situations.

SQL Server