Creating a Comma-Delimited String in SQL Server

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


0 Responses to “Creating a Comma-Delimited String in SQL Server”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

TwitterCounter for @anthonyrstevens
Add to Technorati Favorites

RSS Feed

View Anthony Stevens's profile on LinkedIn

%d bloggers like this: