SQL Jointure récursive avec regroupement

Question

Dans SQL Server, j'ai la table suivante:

Nom   Nouveau_Nom
---------------
A         B
B         C
C         D
G         H
H         I
Z         B

Je veux créer une nouvelle table qui lie tous les noms qui sont reliés à un seul GroupID

GroupID  Nom
-------------
1         A
1         B
1         C
1         D
1         Z
2         G
2         H
2         I

Je suis un peu bloqué sur ça et je ne sais pas comment le faire correctement, apart from a bunch of joins. Mais je voudrais le faire proprement.

J'ai modifié la question pour permettre le regroupement à partir de deux points de départ différents, A et Z, dans un même groupe.

Answer 1

Depuis que vous avez modifié la question, je mets à jour la réponse. Veuillez noter que la réponse est la même en termes de structure logique. Tout ce qui est différent, c'est qu'au lieu d'aller de G à I pour calculer les niveaux, la réponse calcule maintenant de I à G.

<strong><a href="http://rextester.com/UPL4036" rel="nofollow noreferrer">Lien vers la démo en fonctionnement</a></strong>

;with cte as
( 
    select 
       t1.Name as Name, row_number() over (order by t1.Name) r,
       t1.New_Name as New_Name,
       1 as level 
    from yt t1 left join  yt t2
       on t1.New_Name=t2.name
    where t2.name is null
     union all
    select 
       yt.Name as Name, r,
       yt.New_Name as New_Name,
       c.level+1 as level
    from cte c  join yt
       on yt.New_Name=c.Name
   ),
   cte2 as 
   (   
     select r as group_id, Name from cte
     union
     select c1.r as group_id, c1.New_name as Name from cte c1
       where level = (select min(level) from cte c2 where c2.r=c1.r)
     )

 select * from cte2;

Voici l'ancienne réponse:

Vous pouvez essayer la requête basée sur les CTE ci-dessous :

create table yt (Name varchar(10),  New_Name varchar(10));
insert into yt values
('A','B'),
('B','C'),
('C','D'),
('G','H'),
('H','I');

;with cte as
( 
    select 
       t1.Name as Name, row_number() over (order by t1.Name) r,
       t1.New_Name as New_Name,
       1 as level 
    from yt t1 left join  yt t2
       on t1.Name=t2.New_name
    where t2.new_name is null
     union all
    select 
       yt.Name as Name, r,
       yt.New_Name as New_Name,
       c.level+1 as level
    from cte c  join yt
       on yt.Name=c.New_Name
   ),
 cte2 as 
 (   
   select r as group_id, Name from cte
   union
   select c1.r as group_id, c1.New_name as Name from cte c1
     where level = (select max(level) from cte c2 where c2.r=c1.r)
   )

 select * from cte2;

voir la démo en fonctionnement

Answer 2

Peu compliqué mais fonctionnant.

DECLARE @T TABLE  (Nom VARCHAR(2),   Nouveau_Nom VARCHAR(2))
INSERT INTO @T
VALUES
('A','B'),
('B','C'),
('C','D'),
('G','H'),
('H','I'),
('Z','B')

;WITH CTE AS
(
    SELECT * , RN = ROW_NUMBER() OVER(ORDER BY Nom) FROM @T
)
,CTE2 AS (SELECT T1.RN, T1.Nom Nom1, T1.Nouveau_Nom Nouveau_Nom1, 
                 X.Nom Nom2, X.Nouveau_Nom Nouveau_Nom2, 
                 FLAG = CASE WHEN X.Nom  IS NULL THEN 1 ELSE 0 END 
          FROM CTE T1
          OUTER APPLY (SELECT  * FROM CTE T2 WHERE T2.RN > T1.RN 
                       AND (T2.Nom IN (T1.Nom , T1.Nouveau_Nom) 
                            OR T2.Nouveau_Nom IN (T1.Nom , T1.Nouveau_Nom)
                       ))  AS X
)
,CTE3 AS (SELECT *, 
    GroupID = ROW_NUMBER() OVER (ORDER BY RN) -
          ROW_NUMBER() OVER (PARTITION BY Flag ORDER BY RN) +1
    FROM CTE2
)
SELECT 
    DISTINCT GroupID, Nom 
FROM 
  (SELECT * FROM CTE3 WHERE Nom2 IS NOT NULL) SRC 
  UNPIVOT ( Nom FOR COL IN ([Nom1], [Nouveau_Nom1], [Nom2], [Nouveau_Nom2])) UNPVT

Résultat

GroupID              Nom
-------------------- ----
1                    A
1                    B
1                    C
1                    D
1                    Z
2                    G
2                    H
2                    I