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

You could create a temporary table_flatuserpermissions of:

UserID
PermissionID1
Rights1
PermissionID2
Rights2
...etc to as many permission/right combinations as you need

Insert records to this table from Table_user with all permission & rights fields null.

Update records on this table from table_userpermissions - first record insert and set PermissionID1 & Rights1, Second record for a user update PermissionsID2 & Rights2, etc.

Then you query this table to generate your report.

Personally, I'd just stick with the UserId, UserName, PermissionID, Rights columns you have now.

Maybe substitute in some text for PermissionID and Rights instead of the numeric values.

Maybe sort the table by PermissionID, User instead of User, PermissionID so the auditor could check the users on each permission type.

Answer 3

Short answer:

No.

You can't dynamically add columns in to your query.

Remember, SQL is a set based language. You query sets and join sets together.

What you're digging out is a recursive list and requiring that the list be strung together horizontally rather then vertically.

You can, sorta, fake it, with a set of self joins, but in order to do that, you have to know all possible permissions before you write the query...which is what the other suggestions have proposed.

You can also pull the recordset back into a different language and then iterate through that to generate the proper columns.

Something like:

SELECT Table_User.userID, userName, permissionid, rights
FROM Table_User
        LEFT JOIN Table_UserPermissions ON Table_User.userID =Table_UserPermissions.userID
ORDER BY userName

And then display all the permissions for each user using something like (Python):

userID = recordset[0][0]
userName = recordset[0][1]
for row in recordset:
   if userID != row[0]:
       printUserPermissions(username, user_permissions)
       user_permissions = []
       username = row[1]
       userID = row[0]

    user_permissions.append((row[2], row[3]))

printUserPermissions(username, user_permissions)

Answer 4

If it's acceptable, a strategy I've used, both for designing and/or implementation, is to dump the query unpivoted into either Excel or Access. Both have much friendlier UIs for pivoting data, and a lot more people are comfortable in that environment.

Once you have a design you like, then it's easier to think about how to duplicate it in TSQL.

Answer 5

It seems like the pivot function was designed for situations where you can use an aggregate function on one of the fields. Like if I wanted to know how much revenue each sales person made for company x. I could sum up the price field from a sales table. I would then get the sales person and how much revenue in sales they have. For the permissions though it doesn't make sense to sum/count/etc up the permissionId field or the Rights field.

Answer 6

You may want to look at the following example on creating cross-tab queries in SQL:

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

It looks like there are new operations that were included as part of SQL Server 2005 called PIVOT and UNPIVOT

Answer 7

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.