I love 4 Guys From Rolla. Most of the time when I have a particular tool I’m looking for, I can find it.
In this case, I had a many-to-many relationship that I wanted to join together to concatenate one half of the relationship to get a comma-delimited string. The DB entities are JOB and KEYWORD. One job can have many keywords, and one keyword can be associated with many jobs. I use a standard join table, JOB_X_KEYWORD, to accomplish this in the DB.
Here’s the code, with a hat tip to Scott Mitchell:
CREATE FUNCTION dbo.udf_GetKeywords(@jobid int)
RETURNS VARCHAR(1000) AS
DECLARE @k varchar(1000)
SELECT @k = COALESCE(@k + ', ', '') + k.keyword_value
FROM keyword k JOIN job_x_keyword jxk ON (k.keyword_id = jxk.keyword_id)
WHERE jxk.job_id = @jobid
select j.title, dbo.udf_getkeywords(j.job_id)
from job j