SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values (1, 'james', 'A', 'B', 'bond'),        
             (2, 'james', 'B', 'C', 'bond'),
             (3, 'james', 'X', 'Z', 'bond')
) v(id, firstname, startpoint, endpoint, lastname )
3 rows affected

select firstname, lastname, string_agg(pt, ', ') as points
from (select t.*, v.pt,
             row_number() over (partition by firstname, lastname, pt order by pt) as seqnum
      from t cross apply
           (values (t.startpoint), (t.endpoint)) as v(pt)
     ) t
where seqnum = 1
group by firstname, lastname;


firstname
lastname
points


james
bond
A, B, C, X, Z


with cte as (
      select id, firstname, lastname,
             convert(varchar(max), concat(startpoint, ', ', endpoint)) as points,
             endpoint, 1 as lev
      from t
      where not exists (select 1 from t t2 where t2.endpoint = t.startpoint)
      union all
      select cte.id, cte.firstname, cte.lastname,
             concat(cte.points, ', ', cte.endpoint), t.endpoint, lev + 1
      from cte join
           t
           on t.startpoint = cte.endpoint 
    )
select top (1) with ties *
from cte
order by row_number() over (partition by id order by lev desc)


id
firstname
lastname
points
endpoint
lev


1
james
bond
A, B, B
C
2

3
james
bond
X, Z
Z
1