ChrisC ChrisC - 6 months ago 8
SQL Question

How to return a single row aggregating data in multiple rows in SQL

I have a table of UserIds and rolenames.

For example:

UserId Rolename
1 Admin
1 Editor
1 Other
2 Admin
3 Other


I want to return a single row per user containing
UserId, IsAdmin, IsEditor
, where the latter two columns are booleans representing whether or not the user has the "Admin" role or "Editor" role.

From the above example I would get the following output:

UserId IsAdmin IsEditor
1 True True
2 True False
3 False False


Any thoughts? I've been trying all sorts of things with aggregate functions in group by, sub selects etc., but I'm just not getting it.

Answer

users :

  UserId   UserName

    1        amir  
    2        john  
    3        sara  

userRoles :

  UserId   RoleName

    1        Admin  
    1        Editor  
    2        Editor  

query :

   select UserId , 
     (select count(UserRoles.UserId) from userRoles where userRoles.UserId=users.UserId and RoleName='Admin' ) as IsAdmin ,
     (select count(userRoles.UserId) from userRoles where userRoles.UserId=users.UserId and RoleName='Editor' ) as IsEditor
    from users;

result :

 UserId      IsAdmin   IsEditor
    1          1          1
    2          0          1
    3          0          0