CODEWITHSUNDEEP
sqlsql-server
user3102032
user3102032

Reputation: 11

How to combine the result of SQL server table rows using bitwise operator?

My table is

ID | RoleID | PermissionName | CanRead | CanWrite | CanUpdate |
 1 |      1 | Page1          |    1    |    0     |     1     |
 2 |      1 | Page2          |    1    |    0     |     0     |
 3 |      2 | Page1          |    0    |    1     |     0     |
 4 |      2 | Page2          |    1    |    0     |     1     |

If a user has 2 roles ex: RoleID 1, 2

So he has Read, Write, Update for Page1 and Read&Update for Page2 PermissionName

If I pass role ids to a stored procedure how to get the combined result as like below?

Ex:

PermissionName | CanRead | CanWrite | CanUpdate |
      Page1    |    1    |     1    |     1     |
      Page2    |    1    |     0    |     1     |

and the user may have n number of roles, according to the roles he is having list the combined permissions for each PermissionName

Thanks

Upvotes: 1

Views: 99

Answers (2)

Daniel B
Daniel B

Reputation: 807

Try this:

SELECT PermissionName, 
CASE WHEN sum(CanRead)   = 0 THEN 0 ELSE 1 END CanRead, 
CASE WHEN sum(CanWrite)  = 0 THEN 0 ELSE 1 END CanWrite, 
CASE WHEN sum(CanUpdate) = 0 THEN 0 ELSE 1 END CanUpdate 
FROM MyTable 
WHERE ID = DesiredId 
GROUP BY PermissionName

Upvotes: 2

fasadat
fasadat

Reputation: 1045

You can do something like this in your stored procedure

Declare @canRead int
Declare @canWrite int
Declare @canUpdate int

SELECT
    Case canRead = 1 then @canread = @canRead + 1  
    END canRead 
FROM 
    yourtablename 
WHERE 
    id = desiredID

SELECT 
    permissionName, 
    Case @canReasd >= 1 THEN 1 ELSE 0 END canRead 
FROM yourTableName 
WHERE id = desiredID

Upvotes: 0

Related Questions