SQL query help with bridge table

Question

I am working with an existing database and am trying to write a sql query to get all account information, including permission levels. This is for security auditing. We want to easily dump all this information so that it is easy to compare it. My problem is that there is a bridge / link table for permissions, so there are several entries for each user. I want to return the results with all permissions 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 references a table with the name of the permission and what it does. Right as 1 = view, 2 = change, etc.

What I get from the basic request for user 1:

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

What I would like to do is:

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

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

Is there any other way with sql that I can do?

Answer 1

You can 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 must explicitly add a max (...) column for each resolution.

Answer 2

Short answer:

Not.

You cannot dynamically add columns to your query.

Remember that SQL is a set-based language. You execute sets of queries and combine multiple sets.

What you dig up is a recursive list and requires the list to be drawn horizontally, not vertically.

You can, sort, fake it using a set of independent connections, but for this you need to know all the possible permissions before writing a request ... that’s what other suggestions suggested.

You can also output the recordset to another language and then iterate over it to create the corresponding 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 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 3

If you are using MySQL, I would suggest using group_concat () as shown 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 

It will return

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

A quick google search for mssql group_concat showed several different stored procedures ( I ), ( II ) for MSSQL, which can achieve the same behavior.

Answer 4

You can create a temporary table table_flatuserpermissions:

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

Insert records into this table from Table_user with all permission and null fields.

Update records in this table from table_userpermissions - insert the first record and set PermissionID1 and Rights1, the second record for user-updated PermissionsID2 and Rights2, etc.

You then query this table to create the report.

Personally, I just stick with the identifiers UserId, UserName, PermissionID, the rights that you have.

It can be replaced in some text for PermissionID and Rights instead of numeric values.

Perhaps sort the table using PermissionID, User instead of User, PermissionID so that the auditor can check users for each type of permission.

Answer 5

If this is acceptable, the strategy that I used, both for design and implementation, is to drop a query that is not bound to Excel or Access. Both have much friendlier user interfaces for rotating data, and there are a lot more people in this environment.

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

Answer 6

It seems that the support function was designed for situations where you can use the aggregate function in one of the fields. For example, if I wanted to know how much revenue each seller made for company x. I could summarize the price field from the sales table. Then I get the seller and how much sales revenue they have. For permissions, although it makes no sense to sum / count / etc to the permissionId field or the Rights field.

Answer 7

You might want to look at the following example when creating cross-site table 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 8

For this type of data conversion, you need to perform both UNPIVOT and PIVOT data. If you know the values ​​you want to convert, you can program the query using a static bar, otherwise you can use dynamic sql.

Create tables:

 CREATE TABLE Table_User ([UserId] int, [UserName] varchar(5)) ; INSERT INTO Table_User ([UserId], [UserName]) VALUES (1, 'John'), (2, 'Joe'), (3, 'James') ; CREATE TABLE Table_UserPermissions ([UserId] int, [PermissionId] int, [Rights] int) ; INSERT INTO Table_UserPermissions ([UserId], [PermissionId], [Rights]) VALUES (1, 10, 1), (1, 11, 2), (1, 12, 3), (2, 11, 2), (2, 12, 3), (3, 10, 2) ; 

Static PIVOT:

 select * from ( select userid, username, value, col + '_'+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) src pivot ( max(value) for col in (permissionid_1, rights_1, permissionid_2, rights_2, permissionid_3, rights_3) ) piv order by userid 

See SQL Fiddle with Demo

Dynamic PIVOT:

If you have an unknown number of permissionid and rights , you can use dynamic sql:

 DECLARE @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsPivot = STUFF((SELECT ',' + quotename(c.name +'_'+ cast(t.rn as varchar(10))) from ( select row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) t cross apply sys.columns as C where C.object_id = object_id('Table_UserPermissions') and C.name not in ('UserId') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select userid, username, value, col + ''_''+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) x1 pivot ( max(value) for col in ('+ @colspivot +') ) p order by userid' exec(@query) 

See SQL Fiddle for a demo.

Result for both:

 | USERID | USERNAME | PERMISSIONID_1 | RIGHTS_1 | PERMISSIONID_2 | RIGHTS_2 | PERMISSIONID_3 | RIGHTS_3 | --------------------------------------------------------------------------------------------------------- | 1 | John | 10 | 1 | 11 | 2 | 12 | 3 | | 2 | Joe | 11 | 2 | 12 | 3 | (null) | (null) | | 3 | James | 10 | 2 | (null) | (null) | (null) | (null) |