Combine Records to one field

[Pauldohert]

I have been asked to combine records into one field - I have written a function and then used this per listID in a view to get the records per listID in a single record field. This all works OK.

However is there a better way of doing this as I seem to remeber reading that using a function in this way wasn't particularly recommended.

Ta


CREATE FUNCTION dbo.fnList(@pID as Int)
RETURNS VarChar(8000)

AS
BEGIN

DECLARE @OUTPUT VARCHAR(8000)
SET @OUTPUT = ''

SELECT @OUTPUT = CASE @OUTPUT
WHEN '' THEN IsNull(ListName,'')
ELSE @OUTPUT + ',' + IsNull(ListName,'')
END
FROM View_List
WHERE ListID = @pID
ORDER BY ListID




RETURN @OUTPUT
END