I have two tables in SQL Server 2012

Table1

Table2

Each user can have between 1 and 8 Permissions.

I need to create a view that will give me the UserID with 8 permission entries with any unused entries containing null values i.e. The order of the entries does not matter.

This has me stumped, I don't even know where to start?

CodePudding user response：

This should do what you want.

SELECT f.UserID, (SELECT top(1) Permission from Table_2 t where t.UserID = f.UserID order by Permission desc) as Permit1
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 1 row FETCH NEXT 1 ROWS ONLY) as Permit2
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 2 row FETCH NEXT 1 ROWS ONLY) as Permit3
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 3 row FETCH NEXT 1 ROWS ONLY) as Permit4
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 4 row FETCH NEXT 1 ROWS ONLY) as Permit5
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 5 row FETCH NEXT 1 ROWS ONLY) as Permit6
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 6 row FETCH NEXT 1 ROWS ONLY) as Permit7
, (SELECT Permission from Table_2 t where t.UserID = f.UserID order by Permission desc offset 7 row FETCH NEXT 1 ROWS ONLY) as Permit8
from Table1 as f

I put in your data, and added one more entry for user id 2 and it returned like this.

UserID   Permit1 Permit2 Permit3 Permit4 Permit5 Permit6 Permit7 Permit8
1        P15     NULL     NULL     NULL  NULL     NULL     NULL     NULL
2        P5      P330     P15      NULL  NULL     NULL     NULL     NULL

CodePudding user response：

You can do this easily with conditional aggregation. This has been asked and answered hundreds of times but if it is a new concept it would be hard to know what search terms to use. This is complete with consumable sample data.

declare @Users table
(
    UserID int
    , Name varchar(10)
)

insert @Users values
(1, 'Joe')
, (2, 'Mary')

declare @Permissions table
(
    UserID int
    , Permission varchar(10)
)

insert @Permissions values
(1, 'P15')
, (2, 'P5')
, (2, 'P330')


select x.UserID
    , Permit1 = max(case when x.RowNum = 1 then x.Permission end)
    , Permit2 = max(case when x.RowNum = 2 then x.Permission end)
    , Permit3 = max(case when x.RowNum = 3 then x.Permission end)
    , Permit4 = max(case when x.RowNum = 4 then x.Permission end)
    , Permit5 = max(case when x.RowNum = 5 then x.Permission end)
    , Permit6 = max(case when x.RowNum = 6 then x.Permission end)
    , Permit7 = max(case when x.RowNum = 7 then x.Permission end)
    , Permit8 = max(case when x.RowNum = 8 then x.Permission end)
from
(
    select u.UserID
        , p.Permission
        , RowNum = ROW_NUMBER() over(partition by u.UserId order by p.Permission)
    from @Users u
    join @Permissions p on p.UserID = u.UserID
) x
group by x.UserID