Since I recently posted a Split UDF, I figured I'd do some research on the best way to do a Join operation. Unfortunately, UDF's can't take table variables as input parameters, so I had to just write a snippet of straight sql. Here's the solution:
DECLARE @valueList varchar(1000)
SELECT
@valueList = COALESCE(@valueList + ',', '') + CAST(Field AS varchar)
FROM
Table
The only way I'd seen before to do this operation involved a cursor, so it was great to come across this idea. Basically, the use of COALESCE function makes this work. For the first row, it returns an empty string, as @valueList is initially null. This makes the result 'val1'. Then, for subsequent rows, it returns the value with a comma appended, so the result is a comma seperated list. In one statement!