Como posso consolidar os mapeamentos relacionados?

Supondo ..

• você só tem 3 níveis
• cada letra pode existir em apenas 1 nível
• relacionamentos implícitos são transitivos
  • por exemplo a->b->c, ∪ p->b->e⇒ { a->b->e, p->b->c}

... então o seguinte deve ser suficiente:

É um pouco confuso, mas aqui está um dbfiddle completo usando o exemplo do conjunto de dados "editar".

Primeiro, isole apenas os registros que comunicam um relacionamento (qualquer linha com 2 ou 3 não- NULLvalores) desta forma . As linhas singleton podem comunicar a existência de uma entidade órfã, mas não fornecerão nenhuma informação de mapeamento relevante.

(
  select 
    '1->2',
    idtype1,
    idtype2
  from mappings
  where idtype1 is not null
    and idtype2 is not null
)
union all
(
  select 
    '2->3',
    idtype2,
    idtype3
  from mappings
  where idtype2 is not null
    and idtype3 is not null
)
union all
(
  select 
    '1->3',
    idtype1,
    idtype3
  from mappings
  where idtype1 is not null
    and idtype3 is not null
)

Isolar apenas essas linhas nos dá.

A partir daí, infira relacionamentos ausentes. Como assumimos que existem apenas 3 níveis, os únicos relacionamentos possíveis para pular níveis são aqueles com o 1->3rótulo. por exemplo

  select * 
  from relationships
  where ancestor_level = 1
    and descendant_level = 3

Depois de normalizar os relacionamentos E preencher todas as lacunas, você se une aos relacionamentos 1->2& 2->3para obter o conjunto completo, conforme visto abaixo.

select 
  r1.ancestor_name as level_1,
  r2.ancestor_name as level_2,
  r2.descendant_name as level_3
from relationships as r1
join relationships as r2 on r2.ancestor_name = r1.descendant_name
order by 1, 2, 3

Dada esta abordagem, assumimos a solução abaixo:

Onde as linhas vermelhas são inferidas.

Esta solução tem algumas questões e advertências em aberto.

1. Se um p->arelacionamento fosse introduzido, a transitividade se aplicaria de tal forma que a->qagora fosse válida?
2. Esta solução funciona apenas para 3 níveis. Um quarto nível exigiria descoberta adicional de relacionamento. À medida que os níveis aumentam, torna-se um problema de recursão hierárquica mais padrão - cuja resposta depende se p->a⇒a->q

Um exemplo de auto-junção vs. recursão para inferir hierarquia pode ser lido mais detalhadamente em Find Ancestry From JSON

Se você estiver atrás de uma única consulta que produzirá o resultado esperado, provavelmente precisará fazer algo assim (que é essencialmente construir um conjunto de cada valor de "tipo" distinto e não nulo e, em seguida, gerar o interseção de cada um).

;WITH 
t1 AS (SELECT DISTINCT idType1 FROM mapTable WHERE idType1 Is Not Null),
t2 AS (SELECT DISTINCT idType2 FROM mapTable WHERE idType2 Is Not Null),
t3 AS (SELECT DISTINCT idType3 FROM mapTable WHERE idType3 Is Not Null)
SELECT
*
FROM t1
CROSS APPLY t2
CROSS APPLY t3;

Você fala sobre os diferentes valores terem "links" entre si, o que, admito, para mim, não faz todo o sentido - mas, seguindo sua "saída esperada", presumo que sejam as combinações possíveis de cada tipo1 , type2, type3 valor que você deseja