Declare @source As Table (che nvarchar(2), con int);
Insert Into @source (che, con) Values
(N'a', 1), (N'a', 2), (N'a', 3), (N'b', 1), (N'b', 2), (N'b', 4), (N'c', 3), (N'c', 5), (N'c', 6), (N'd', 7), (N'd', 8), (N'd', 9), (N'e', 7), (N'e', 10), (N'f', 8), (N'f', 11), (N'g', 9), (N'g', 12), (N'h', 13), (N'h', 14), (N'h', 15), (N'i', 16), (N'i', 17), (N'j', 18), (N'k', 6), (N'k', 19), (N'l', 19), (N'l', 20), (N'm', 5), (N'm', 4);
With conJoined As (
Select Distinct tl.con,
IIF(tl.che < tr.che, tl.che, tr.che) as [from],
IIF(tl.che > tr.che, tl.che, tr.che) As [to]
From @source tl
Inner Join @source tr On (tl.con = tr.con)
),
childs As (
Select con, [from], [to]
From conJoined
Where [from] <> [to]
),
exclude As (
Select Distinct [to]
From childs
),
roots As (
Select tj.con, tj.[from], tj.[to]
From conJoined tj
Left Join exclude te On (tj.[to] = te.[to])
Where te.[to] Is Null And tj.[from] = tj.[to]
),
cilentIds As (
Select tu.[from],
Row_Number() Over(Order By tu.[from]) As clientId
From
(Select Distinct [from] From roots) tu
),
recursiveQuery As (
Select con, [from] As [root], [to] from roots
Union All
Select tc.con, tr.[root], tc.[to]
From childs tc
Inner Join recursiveQuery tr On (tr.[to] = tc.[from])
)
Select Distinct tc.clientId, tr.con From recursiveQuery tr
Inner Join cilentIds tc On (tr.[root] = tc.[from])
Order By tc.clientId, tr.con;
|