根据1-N关系相邻对齐表

SELECT a.*, b.*
FROM t2
LEFT JOIN t1 a ON t2.Service = a.Service
LEFT JOIN t1 b ON t2.Related = b.Service
ORDER BY a.value, b.value;

这看起来像是我想要的。使用行号()帮助了我!

if object_id('tempdb..#a', 'U') is not null 
    drop table #a;

select *, row_number() over (partition by Service order by Value) Row_Num into #a from #Service

select 
    isnull(a.service, b.s) Service, 
    a.Value, 
    isnull(b.Service, a.r) Related_Service, 
    b.Value
from
(
    select a.Service, Value, Row_Num, b.Service s, b.Related r from #a a right join #Service_Related b on a.Service = b.Service 
) a
full outer join
(
    select a.Service, Value, Row_Num, b.Service s, b.Related r from #a a right join #Service_Related b on a.Service = b.Related
) b
on a.s = b.s and a.r = b.r and a.Row_Num = b.Row_Num
order by a.Service, b.Service