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