SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ExternalPersonRelationTable
(
  PersonId int,
  SubjectCode int
);

insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);

select * from ExternalPersonRelationTable;


PersonId
SubjectCode


4187
3

4187
278

4429
3

4429
4

4463
99

4464
174

4464
175


-- static pivot

select piv.SubjectCode as Code,
       isnull(convert(bit, piv.[4187]), 0) as [4187],
       isnull(convert(bit, piv.[4429]), 0) as [4429],
       isnull(convert(bit, piv.[4463]), 0) as [4463],
       isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;


Code
4187
4429
4463
4464


3
True
True
False
False

4
False
True
False
False

99
False
False
True
False

174
False
False
False
True

175
False
False
False
True

278
True
False
False
False


-- dynamic version

-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);

with cte as
(
  select epr.PersonId
  from ExternalPersonRelationTable epr
  group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']), 0) as ['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ', ') within group (order by cte.PersonId),
       @pivotList = string_agg('['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ',') within group (order by cte.PersonId)
from cte;

-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;

-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
                              + @fieldList
                              + 'from ExternalPersonRelationTable epr '
                              + 'pivot (max(epr.PersonId) for epr.PersonId in ('
                              + @pivotList
                              + ')) piv;';
      
-- validate query
select @query as Query;

-- run query
exec sp_executesql @query;


FieldList


isnull(convert(bit, piv.[4187]), 0) as [4187], isnull(convert(bit, piv.[4429]), 0) as [4429], isnull(convert(bit, piv.[4463]), 0) as [4463], isnull(convert(bit, piv.[4464]), 0) as [4464]




PivotList


[4187],[4429],[4463],[4464]




Query


select piv.SubjectCode as Code, isnull(convert(bit, piv.[4187]), 0) as [4187], isnull(convert(bit, piv.[4429]), 0) as [4429], isnull(convert(bit, piv.[4463]), 0) as [4463], isnull(convert(bit, piv.[4464]), 0) as [4464]from ExternalPersonRelationTable epr pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;




Code
4187
4429
4463
4464


3
True
True
False
False

4
False
True
False
False

99
False
False
True
False

174
False
False
False
True

175
False
False
False
True

278
True
False
False
False
Code	4187	4429	4463	4464
3	True	True	False	False
4	False	True	False	False
99	False	False	True	False
174	False	False	False	True
175	False	False	False	True
278	True	False	False	False