SQL query help with bridge table

Question

I'm working with a existing database and trying to write a sql query to get out all the account information including permission levels. This is for a security audit. We want to dump all of this information out in a readible fashion to make it easy to compare. My problem is that there is a bridge/link table for the permissions so there are multiple records per user. I want to get back results with all the permission for one user on one line. Here is an example:

Table_User:
UserId   UserName
1        John
2        Joe
3        James

Table_UserPermissions:
UserId   PermissionId   Rights
1        10             1
1        11             2
1        12             3
2        11             2
2        12             3
3        10             2

PermissionID links to a table with the name of the Permission and what it does. Right is like 1 = view, 2 = modify, and etc.

What I get back from a basic query for User 1 is:

UserId UserName PermissionId Rights
1      John     10           1
1      John     11           2
1      John     12           3

What I would like something like this:

UserId UserName Permission1 Rights1 Permission2 Right2 Permission3 Right3
1      John     10          1       11          2      12          3

Ideally I would like this for all users. The closest thing I've found is the Pivot function in SQL Server 2005. http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx The problem with this from what I can tell is that I need to name each column for each user and I'm not sure how to get the rights level. With real data I have about 130 users and 40 different permissions.

Is there another way with just sql that I can do this?

Answer 1

You could do something like this:

select userid, username
,      max(case when permissionid=10 then rights end) as permission10_rights
,      max(case when permissionid=11 then rights end) as permission11_rights
,      max(case when permissionid=12 then rights end) as permission12_rights
from   userpermissions
group by userid, username;

You have to explicitly add a similar max(...) column for each permissionid.

Answer 2

If you where using MySQL I would suggest you use group_concat() like below.

select UserId, UserName, 
       group_concat(PermissionId) as PermIdList,
       group_concat(Rights SEPARATOR ',') as RightsList
from Table_user join Table_UserPermissions on 
     Table_User.UserId = Table_UserPermissions.UserId=
GROUP BY Table_User.UserId

This would return

UserId UserName PermIdList  RightsList
1      John     10,11,12    1,2,3

A quick google search for 'mssql group_concat' revealed a couple different stored procedures (I), (II) for MSSQL that can achieve the same behavior.