Requête SQL pour table avec plusieurs lignes par ID sortant une seule ligne par ID

C'est un peu un gratte-tête pour moi. J'ai simplifié les arrays et ajouté un exemple de scénario pour aider le context. J'ai besoin d'écrire une requête dans SQL Server qui produira les résultats dans la troisième table en utilisant datatables de la première table au moyen de la table de reference au centre. Je ne suis pas très malin avec l'écriture de requêtes SQL (mais ça ira certainement mieux), donc toute aide que vous pourriez m'offrir serait géniale! Les arrays sont les suivants:

Voici la table de données qui peut contenir entre une et trois inputs pour une seule identité.

┌────────┬──────────────────┐ │Identity│Partial_Identifier│ ├────────┼──────────────────┤ │100 │a │ ├────────┼──────────────────┤ │100 │b │ ├────────┼──────────────────┤ │100 │c │ ├────────┼──────────────────┤ │101 │b │ ├────────┼──────────────────┤ │102 │b │ ├────────┼──────────────────┤ │102 │c │ └────────┴──────────────────┘ 

Ci-dessous se trouve une table de reference, qui associe des combinaisons d'identificateurs partiels à un IDCode unique (unique) dont j'ai besoin à des fins d'affichage. Le design n'est pas quelque chose que je considère comme idéal, mais c'est préexistant, donc je dois me débrouiller avec.

 ┌──────┬────────────────────┬────────────────────┬────────────────────┐ │IDCode│Partial_Identifier_1│Partial_Identifier_2│Partial_Identifier_3│ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │1 │a │ │ │ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │2 │a │b │ │ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │3 │a │b │c │ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │4 │b │ │ │ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │5 │b │c │ │ ├──────┼────────────────────┼────────────────────┼────────────────────┤ │6 │b │c │d │ └──────┴────────────────────┴────────────────────┴────────────────────┘ 

Pour datatables du premier tableau, je voudrais le résultat suivant:

 ┌────────┬──────┐ │Identity│IDCode│ ├────────┼──────┤ │100 │3 │ ├────────┼──────┤ │101 │4 │ ├────────┼──────┤ │102 │5 │ └────────┴──────┘ 

Toute aide que vous pourriez être en mesure de fournir sur la façon d'aborder ce peu de funkiness serait très appréciée.

Probablement pas le moyen le plus efficace, mais cela fonctionnera:

 declare @a table (id int, p_id nchar(1)) insert @a select 100,'a' union select 100,'b' union select 100,'c' union select 101,'b' union select 102,'b' union select 102,'c' declare @b table (idcode int, p_id1 nchar(1), p_id2 nchar(1), p_id3 nchar(1)) insert @b select 1, 'a', null, null union select 2, 'a', 'b', null union select 3, 'a', 'b', 'c' union select 4, 'b', null, null union select 5, 'b', 'c', null union select 6, 'b', 'c', 'd' select id, idcode from ( select id , max(case when r=1 then p_id end) a , max(case when r=2 then p_id end) b , max(case when r=3 then p_id end) c from ( select id, p_id, row_number() over (partition by id order by p_id) r from @a ) x group by id ) y inner join @bb on coalesce(b.p_id1,'') = coalesce(ya,'') and coalesce(b.p_id2,'') = coalesce(yb,'') and coalesce(b.p_id3,'') = coalesce(yc,'') order by id