cool way to use COALESCE to return comma-delimited list in one-to-many relationship

many times when you have a one-to-many relationship, you want to return a comma-delimited list of the “many” (for example, a list of all the subjects a tutor teaches). so typically your one-to-many join in your query would produce a result set such as {UserId, SubjectId}, and then you would parse this list in application code. a neat approach i saw an example of today is to use sql’s COALESCE function with a bit of string magic to get the same comma-delimited list directly from the database (COALESCE returns the first non-null expression among its arguments). so, if you wanted to return the list subjects that userId 1 teaches, you would have:

declare @SubjectList varchar(2048)

SELECT @SubjectList =
COALESCE(@SubjectList + ', ', '') + pg.GroupName

FROM TableGroups pg

SELECT IsNull(@SubjectList, '')

you can then put this logic in a UDF and call it from a select statement. for more details, see here: http://www.sqlteam.com/item.asp?ItemID=2368