add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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;
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