MSSQL join tables w bit

Question

I have three tables : 1) UserTable

UserId UserName 
  1      Mike
  2      John
  3      Jennifer

2) FormName

fID   fName
 1     edit
 2     cafe
 3     backoffice

3)User to form

fId  UserId   Allowed(bit)
 1     1         0
 2     1         1
 3     1         1
 2     2         1 
 3     2         0

The first table is the user table with user informations. The second table is the form table where it stores form names of application The third table is user level table where it says which user is allowed to open which form .

I want to create sql query where I can see all information in a single table like :

UserId USerName   Edit  Cafe BackOffice
  1      mike      0     1      1  
  2      john      1     1      0

I think it is possbile with SQL Fiddle and Pivot but I am having hard time to figure the right code out .

score 0 · Answer 1 · answered Jun 11 '13 at 00:37

You can use the PIVOT function, but you have to cast the allowed column to something other than a bit datatype. For example, the below casts it to an int:

select userid, username,
  coalesce(Edit, 0) Edit, 
  coalesce(Cafe, 0) Cafe, 
  coalesce(BackOffice, 0)  BackOffice
from
(
  select u.userid, 
    u.username, 
    f.fname, 
    cast(allowed as int) allowed
  from usertable u
  inner join user_form uf
    on u.userid = uf.userid
  inner join formname f
    on uf.fid = f.fid
) d
pivot
(
  max(allowed)
  for fname in (Edit, Cafe, BackOffice)
) piv;

See SQL Fiddle with Demo

MSSQL join tables w bit

1 Answers1