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.