How to write this sql server query

Question

I have three tables employeemaster, roles, employeeroles as shown here:

employeemaster table:

       id  name  isactive
      -------------------
       1    nixon   1
       2    jane    1
       3    bama    1
       4    kris    0

roles table:

      id  roleid rolename
      -----------------
      1    1    admin
      2    2     hr
      3    3    normal

employeeroles table:

     id  roleid  empid
     ------------------
     1    1        1
     2    2        2
     3    3        3
     4    3        4

Desired result:

    id  empid  empname  admin  hr normal
 ----------------------------------------
    1    1      nixon     yes  no   no
    2    2      jane      no   yes  no
    3    3      bama      no   no   yes

The roles names are dynamic, so the result columns depends up on roles table only.

I tried to create a query but I don't know how to make rows to columns. I'm not good at SQL. So please help me to sort this out. I tried query given below. It will give active employee name, id and all role names

 select em.FirstName, 
        em.EmployeeNo,
        r.RoleName 
 from sEmployeeMaster em 
 JOIN sEmployeeRoles er on em.EmpId = er.EmpId 
 join sRoles r on r.RoleID = er.RoleID 

Answer 1

You can get the result using PIVOT, there are are just a few things I'd suggest to get the final result.

First, since you want the new columns admin, hr, and normal to contain a value of Yes/No, then I'd suggest creating a computed column in your query similar to:

select 
  EmpID = em.id,
  EmpName = em.name,
  RoleName = r.rolename,
  HasRole = 'Yes'
from employeemaster em
inner join employeeroles er
  on em.id = er.empid
inner join roles r
 on er.roleid = r.id
where em.isactive = 1;

See Demo. This query will create a Yes value for each employee/role combination and will give you a value to use in the PIVOT. Once you've have got this, you can easily PIVOT the data:

select 
  EmpId,
  EmpName,
  Admin = IsNull(Admin, 'No'),
  HR = IsNull(HR, 'No'),
  Normal = IsNull(Normal, 'No')
from
(
  select 
    EmpID = em.id,
    EmpName = em.name,
    RoleName = r.rolename,
    HasRole = 'Yes'
  from employeemaster em
  inner join employeeroles er
    on em.id = er.empid
  inner join roles r
    on er.roleid = r.id
  where em.isactive = 1
) d
pivot
(
  max(HasRole)
  for RoleName in (Admin, HR, Normal)
) piv;

See SQL Fiddle with Demo

Now you mentioned that the role names are dynamic, so you'll need to use dynamic SQL to get the final result. I'd also suggest getting 2 lists of the new columns names, one list will have just the names concatenated together. The second list will have the IsNull replacement. The code would be:

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @colsNull AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(rolename) 
                    from roles
                    group by rolename, roleid
                    order by roleid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull 
  = STUFF((SELECT ', IsNull(' + QUOTENAME(rolename) + ', ''No'') as '+QUOTENAME(rolename)
           from roles
           group by rolename, roleid
           order by roleid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT EmpId, EmpName,' + @colsNull + ' 
            from 
            (
              select 
                EmpID = em.id,
                EmpName = em.name,
                RoleName = r.rolename,
                HasRole = ''Yes''
              from employeemaster em
              inner join employeeroles er
                on em.id = er.empid
              inner join roles r
                on er.roleid = r.id
              where em.isactive = 1
            ) x
            pivot 
            (
                max(HasRole)
                for RoleName in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. Both versions give a result:

| EMPID | EMPNAME | ADMIN |  HR | NORMAL |
|-------|---------|-------|-----|--------|
|     1 |   nixon |   Yes |  No |     No |
|     2 |    jane |    No | Yes |     No |
|     3 |    bama |    No |  No |    Yes |

Answer 2

SELECT e.id,e.name,
(CASE WHEN er.roleid=1 THEN 'Yes' ELSE 'No' END)as admin,
(CASE WHEN er.roleid=2 THEN 'Yes' ELSE 'No'END)as hr,
(CASE WHEN er.roleid=3 THEN 'Yes' ELSE 'No'END)as normal
FROM  employeemaster e
JOIN employeeroles er
ON e.id=er.empid
WHERE e.isactive=1