SQL query help with bridge table

By : MaxGeek
Source: Stackoverflow.com
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?

By : MaxGeek


Answers

For this type of data transformation you will need to perform both an UNPIVOT and then a PIVOT of the data. If you know the values that you want to transform, then you can hard-code the query using a static pivot, 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 permissionids and rights, then 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 with demo

The result for both is:

| 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) |
By : bluefeet


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.

By : Zoredache


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

By : Kyle


This video can help you solving your question :)
By: admin