How to create a comma seperated list from a resultset in one statement
February 6. 2007 0 Comments
- Posted in:
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:
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!