Creating a delimited field from a number of rows

by Joe Havelick 24. July 2009 09:55

I found a couple of tricks in solving the problem of generating a single comma separated field from a number of rows. The function looks a little like:

CREATE FUNCTION ReturnAllNAICs

(     @CompanyID int)

RETURNS nvarchar(128)

AS

BEGIN

      DECLARE @NAICS NVARCHAR(128)

 

      SELECT @NAICS = COALESCE(@NAICS + ', ', '') + NAIC

      FROM NAIC_Codes

      WHERE CompanyID=@CompanyID

 

      RETURN @NAICS

END

So how do we generate the list? Simply appending to a string with each row added. But this always gets 10 times more complex because you don’t know whether to add a comma. That’s where my favorite new keyword COALESCE comse into play. You can read the details below, but basically it’s like an IIF statement but it assesses for the value to be null or not null. So, it will add a comma before the next value only if @NAICS is not NULL.

References:
COALESCE (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms190349.aspx
Using COALESCE to Build Comma-Delimited String - http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Tags:

Tech Tips

Comments are closed

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations