sql - Create view based on three tables with columns as rows in one of tables -

i'm starting learn sql, learning views. wondering if possible join 3 tables in 1 view, columns based on rows of these tables.

i have 3 tables:

roles:

id | name  | permissionid 1  | admin | 1 2  | staff | 2 

rolepermissions:

id | roleid | permissionid 1  | 1      | 1 2  | 1      | 2 3  | 1      | 3 4  | 2      | 1 5  | 2      | 2      <- staff doesn't have permission 3 

permissions:

id | name 1  | perm1 2  | perm2 3  | perm3 . . (not fixed number of permissions) 

i create view this:

id (of role) | name  | perm1 | perm2 | perm3  ... (not fixed number of columns) 1            | admin | true  | true  | true 2            | staff | true  | true  | false 

is possible?

you cannot use view if don't know how many columns output. code below should in right direction. use in procedure dynamic sql if need dynamically build list of columns

; roles(id, name, permissionid) ( select * (values(1, 'admin', '1'), (2, 'staff', '2')) r(id, name, permissionid) ), rolepermissions(id, roleid, permissionid) ( select * (values(1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 2, 1), (5, 2, 2)) p(id, roleid, permissionid) ), permissionss(id, name) ( select * (values(1, 'perm1'), (2, 'perm2'), (3, 'perm3')) p(id, name) ), data as( select r.id, rp.permissionid, p.name roles r inner join rolepermissions rp on rp.roleid = r.id inner join permissionss p on rp.permissionid = p.id ) select piv.id [id of role], r.name , [perm1] = case when [perm1] not null 'true' else 'false' end , [perm2] = case when [perm2] not null 'true' else 'false' end , [perm3] = case when [perm3] not null 'true' else 'false' end data pivot( max(permissionid) name in ( [perm1], [perm2], [perm3]) ) piv inner join roles r on r.id = piv.id