Imagine its like a "You may also know" linkedin relationship where A is connected to B, B to C and C to D. So we need to show that D is a potential connection for A.
SELECT colA
,CASE
WHEN colB <= 'D'
THEN 'D'
ELSE 'G'
END
FROM #SampleData
/*
drop table #SampleData
create table #SampleData
(
ColA varchar(100) null,
ColB varchar(100) null
)
insert into #SampleData select 'A','B'
insert into #SampleData select 'B','C'
insert into #SampleData select 'C','D'
insert into #SampleData select 'E','F'
insert into #SampleData select 'F','G'