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.