We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.

I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID.

There are three different types of cases that need to be solved.

1. Simple case of A->B->C. In the view, all three of these records would receive the same GroupID
2. Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation.
3. A DefectID has two or more PreviousDefectIDs (comma seprated)

So, the following table:

CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
       ('B', 'A'),
       ('C', 'B'),
       ('D', NULL),
       ('E', 'D'),
       ('F', 'D'),
       ('G', 'D'),
       ('H', 'E'),
       ('I', 'E'),
       ('K', NULL),
       ('L', NULL),
       ('M', NULL),
       ('N', 'L,M'),
       ('O', 'K,N') 

Would result in a view that looks like the following table:

CREATE TABLE #Final (
GroupID INT,
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Final (GroupID, ID, PreviousID)
VALUES (1, 'A', NULL),
       (1, 'B', 'A'),
       (1, 'C', 'B'),
       (2, 'D', NULL),
       (3, 'D', NULL),
       (4, 'D', NULL),
       (2, 'E', 'D'),
       (3, 'F', 'D'),
       (4, 'G', 'D'),
       (7, 'D', NULL),
       (7, 'E', 'D'),
       (7, 'H', 'E'),
       (7, 'I', 'E'),
       (5, 'K', NULL),
       (5, 'L', NULL),
       (5, 'M', NULL),
       (5, 'N', 'L,M'),
       (5, 'O', 'K,N') 

Note, there are more records in the resulting view than in the table, because when a single DefectID leads to multiple other DefectIDs, there is a new branch created for each chain and each gets their own GroupID number.

I know this is a complicated question, any help at all would be appreciated.

I am attaching what I have so far, but it isn't quite correct, and doesn't yet address the third type of situation.

select ID,
PreviousID, count(PreviousID) over (partition by ID, PreviousID) as 'cnt', null
from #temp temp
union all
(
select temp.ID, temp.PreviousID, null, row_number () over (partition by temp.ID, temp.PreviousID order by temp.PreviousID) as 'rn'
from #temp temp inner join #temp temp1 on temp.ID = temp1.PreviousID
)

order by 1