Query that combines several columns into one row based on a join by Id

Question

Given the following table T:

------------------------------
|  Person | PermissionTypeId |
------------------------------
|  Jon   |       1           |
------------------------------
|  Jon   |       2           |
------------------------------
|  Adam  |       1           |
------------------------------

And Table P:

------------------------------
| Id   | PermDescription     |
------------------------------
|  1   |  Can Access Reports |             
------------------------------
|  2   |  Can Access Locker  |         
------------------------------

Suppose I want to write a query that returns one row of Jons permissions like this:

------------------------------------------------------
| Person   | Can Access Reports | Can Access Locker  |
------------------------------------------------------
|  Jon     |      true          |      true          |
------------------------------------------------------

Where true exists if there is a PermissionTypeId associated with the user. And false exists where there is not. Hence Adams query would return:

-----------------------------------------------------
| Person   | Can Access Reports | Can Access Locker |
----------------------------------------------------
|  Adam    |      true          |        false      |
-----------------------------------------------------

How can I write the following query that returns this row?

If the number of columns can differ, you would need to do a dynamic pivot. — Tab Alleman, Jun 18 '18 at 19:09
If it is much easier to have a constant amount of columns where 'false' is in place where there is no permission I can edit my answer to ask for that since it still returns what I want — Jebathon, Jun 18 '18 at 19:10
Yes, if you have a consistent number of columns with either "true" or "false", then its a simple pivot without aggregation, not a dynamic pivot. And that's a bit easier. — Tab Alleman, Jun 18 '18 at 19:12
See if this helps: https://stackoverflow.com/questions/28322228/sql-rows-to-columns-pivot-with-just-bit-values — Tab Alleman, Jun 18 '18 at 19:18

score 0 · Answer 1 · answered Jun 18 '18 at 19:15

You could try this:

declare @T table(person varchar(10), PermissionType int);
insert into @T values
('Jon', 1),
('Jon', 2),
('Adam', 1);
declare @P table(id int, PermDescription varchar(100));
insert into @P values
(1, 'Can Access Reports'),
(2, 'Can Access Locker');

select person,
       case when [Can Access Reports] is null then 'false' else 'true' end [Can Access Reports],
       case when [Can Access Locker] is null then 'false' else 'true' end [Can Access Locker]
from (
    select t.person, t.permissiontype, p.permdescription
    from @T t join @p p on t.PermissionType = p.id
) unpvt pivot (
    max(permissiontype) for permdescription in ([Can Access Reports],[Can Access Locker])
) pvt

score 0 · Answer 2 · answered Jun 18 '18 at 19:22

You can pivot like below See working demo

 select *
    from
     (
      select 
          t.Person,
          p1.PermDescription,
          data = case when max(p2.id) is null then N'false' else N'true' end
      from 
          t cross join  P p1 
           left join P p2
           on p2.id=t.PermissionTypeId and p2.id=p1.id
       group by t.Person,
          p1.PermDescription
       )   src
       pivot 
       ( max(data) for PermDescription in ([Can Access Locker],[Can Access Reports]))p

Query that combines several columns into one row based on a join by Id

2 Answers2