Comma Separated results, without a Cursor

I've been writing T-SQL for years, and I've often found a case where I want to collapse a rowset containing a single column into a comma separated list. I've taken a few swags at it over the years, usually involving using a cursor and an accumulator string. Mostly though, I've just passed the whole rowset up the toolchain to my middleware, and dealt with it at the ColdFusion level. However, today I found a case where that just wasn't an option, so I started digging a bit more, and found a great solution at SQLTeam.com. Here's the basics of their approach:

Say you have user, and a pile of associated permissions, like so:

CREATE TABLE User ( 
  userId int autoincrement not null primary key
  username varchar(100), 
  ... 
)

CREATE TABLE UserPermission( 
  userID int not null,
  permissionID int not null
)

Now say you want to get a comma separated list of permissions for a given user. You might think of using a cursor, or some other obscure method, but there's an easy way:

DECLARE @UserPermissionList varchar(MAX)

SELECT @UserPermissions = COALESCE( @UserPermissions +',', '' ) + CAST( PermissionID as Varchar(12) )
FROM UserPermission
WHERE UserID = @UserID

SELECT User.*, @userPermissionList as PermissionList
FROM User 
WHERE UserID = @userID

Voila, you've got a comma separated list of permissions, without using a cursor. For those of you in the NoSQL crowd, you'll note that really the COALESCE here is functioning very similarly to a "reduce" function from a map/reduce algorithm. This approach could therefore open up some other interesting avenues for design.

I still wish there was a "JOIN()" aggregate function, but until that happens, we're not totally paralyzed.