Archive for May, 2011

My first real exposure to databases was with MySQL.  Every database has its ups and downs and MySQL is no exception, but among its major ups was – to me anyway – a function called Group_Concat.

If you’re not familiar with Group_Concat, consider a basic role based security system.  You have numerous users, each of which has has one or more roles.  It is easy enough to get back a list of user/role pairs, easy enough to get back a list of roles with one user per role, and easy enough to get back a list of users with one role per user; that’s all basic join syntax.

But what if you want a list of roles — one row per role — and want to know every user that has that role?

That’s what Group_Concat is for.  Bicker and argue all you like about why one might or might not need this functionality, there are just times when it’s nice to have.

Unfortunately, MS-SQL doesn’t really implement it… or it didn’t.  Recently I found a sort of hacked together implementation of Group_Concat in MS-SQL that actually works.  Since I’m developing in C# these days, the following implementation applies to a SQL 2008 database implementing ASP.net’s standard role based security.

SELECT 
     RoleName, 
     UserNames 
FROM 
     dbo.aspnet_Roles AS r 
     CROSS APPLY (
          SELECT 
               u.UserName + ',' 
          FROM 
               dbo.aspnet_Users u 
               INNER JOIN dbo.aspnet_UsersInRoles uir 
                    ON u.UserId = uir.UserId 
               INNER JOIN dbo.aspnet_Roles rr 
                    ON rr.RoleId = uir.RoleId 
          WHERE 
               r.RoleId = uir.RoleId FOR XML PATH('')
     ) D(UserNames) 
     GROUP BY 
          RoleName, 
          UserNames

The relevant parts of this are the XML cleverness and the CROSS APPLY, everything else is basic SQL.

The XML bit relies on MS-SQL’s XML support and is, bluntly, a hack. You can play with it independently with queries like this one

SELECT 
     UserName + '' 
FROM 
     dbo.aspnet_Users 
FOR XML PATH('')

Long story short, adding that FOR XML PATH(”) to the query results in instant concatenation of values. So now all that’s left is to properly relate this to the outer query. That’s where the CROSS APPLY comes in. CROSS APPLY allows you to specify what amounts to a derived table that can legally contain references to some outer information, in this case our outer query. That’s necessary to constrain our inner table so as to only get the users we are interested in. We can’t do it outside of the CROSS APPLY because, by then the concatenation has already occurred.