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)
RETURNS VARCHAR(1000) AS
BEGIN
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
RETURN @k
END
go
select j.title, dbo.udf_getkeywords(j.job_id)
from job j

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s





%d bloggers like this: