SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #example (
  id        INT IDENTITY,
  distance  DECIMAL(18,10),
  a         INT,
  b         INT
)

INSERT INTO
  #example (
    distance,
    a,
    b
  )
VALUES
  (0.0,100,114),
  (0.1, 64,125),
  (0.1, 88,100),
  (0.1, 65,125),
  (0.1, 63, 64),
  (0.1, 65, 66),
  (0.2, 63, 66),
  (0.2, 10, 61),
  (0.2, 19, 61),
  (0.2, 30, 61),
  (0.2, 10, 65),
  (0.2, 10, 94),
  (0.2, 19, 65),
  (0.2, 19, 94),
  (0.2, 30, 94),
  (0.2, 60, 94),
  (0.2, 61, 94)
;
SELECT * FROM #example;


id
distance
a
b


1
0.0000000000
100
114

2
0.1000000000
64
125

3
0.1000000000
88
100

4
0.1000000000
65
125

5
0.1000000000
63
64

6
0.1000000000
65
66

7
0.2000000000
63
66

8
0.2000000000
10
61

9
0.2000000000
19
61

10
0.2000000000
30
61

11
0.2000000000
10
65

12
0.2000000000
10
94

13
0.2000000000
19
65

14
0.2000000000
19
94

15
0.2000000000
30
94

16
0.2000000000
60
94

17
0.2000000000
61
94


CREATE TABLE #node (
  row_id       INT,
  old_val      INT,
  new_val      INT,
  link_count   INT
  INDEX node_old_new CLUSTERED (link_count, old_val, new_val, row_id),
);

INSERT INTO
  #node
SELECT
  e.id, twinned.*, COUNT(*) OVER (PARTITION BY e.id)
FROM
  #example  AS e
CROSS APPLY
(
  SELECT e.a AS old_val, e.b AS new_val
  UNION ALL
  SELECT e.b AS old_val, e.a AS new_val
)
  AS twinned
WHERE
  NOT EXISTS (
    SELECT *
      FROM #example AS lookup
     WHERE twinned.new_val IN (lookup.a, lookup.b)
       AND lookup.id < e.id
  )
;
SELECT * FROM #node ORDER BY row_id, old_val;


row_id
old_val
new_val
link_count


1
100
114
2

1
114
100
2

2
64
125
2

2
125
64
2

3
100
88
1

4
125
65
1

5
64
63
1

6
65
66
1

8
10
61
2

8
61
10
2

9
61
19
1

10
61
30
1

12
10
94
1

16
94
60
1


CREATE TABLE #closure (
  val      INT,
  grp      INT,
  row_id   INT,
  depth    INT
  INDEX closure_val_group_row CLUSTERED (val, grp, row_id, depth)
);
WITH
  closure AS
(
  SELECT
    new_val                               AS val,
    DENSE_RANK() OVER (ORDER BY row_id)   AS grp,
    row_id                                AS row_id,
    0                                     AS depth
  FROM
    #node
  WHERE
    link_count = 2
  
  UNION ALL
  
  SELECT
    n.new_val,
    c.grp,
    n.row_id,
    c.depth + 1
  FROM
    closure   AS c
  INNER JOIN
    #node     AS n
      ON  n.old_val    = c.val
      AND n.link_count = 1
)
INSERT INTO
  #closure
SELECT
  *
FROM
  closure
;
SELECT * FROM #closure;


val
grp
row_id
depth


10
3
8
0

19
3
9
1

30
3
10
1

60
3
16
2

61
3
8
0

63
2
5
1

64
2
2
0

65
2
4
1

66
2
6
2

88
1
3
1

94
3
12
1

100
1
1
0

114
1
1
0

125
2
2
0


SELECT
  e.*, g.grp
FROM
  #example   e
CROSS APPLY
(
  SELECT TOP 1
    c.grp
  FROM
    #closure  AS c
  WHERE
    c.val IN (e.a, e.b)
  ORDER BY
    c.row_id
)
  AS g
ORDER BY
  e.id


id
distance
a
b
grp


1
0.0000000000
100
114
1

2
0.1000000000
64
125
2

3
0.1000000000
88
100
1

4
0.1000000000
65
125
2

5
0.1000000000
63
64
2

6
0.1000000000
65
66
2

7
0.2000000000
63
66
2

8
0.2000000000
10
61
3

9
0.2000000000
19
61
3

10
0.2000000000
30
61
3

11
0.2000000000
10
65
2

12
0.2000000000
10
94
3

13
0.2000000000
19
65
2

14
0.2000000000
19
94
3

15
0.2000000000
30
94
3

16
0.2000000000
60
94
3

17
0.2000000000
61
94
3

id	distance	a	b
1	0.0000000000	100	114
2	0.1000000000	64	125
3	0.1000000000	88	100
4	0.1000000000	65	125
5	0.1000000000	63	64
6	0.1000000000	65	66
7	0.2000000000	63	66
8	0.2000000000	10	61
9	0.2000000000	19	61
10	0.2000000000	30	61
11	0.2000000000	10	65
12	0.2000000000	10	94
13	0.2000000000	19	65
14	0.2000000000	19	94
15	0.2000000000	30	94
16	0.2000000000	60	94
17	0.2000000000	61	94

row_id	old_val	new_val	link_count
1	100	114	2
1	114	100	2
2	64	125	2
2	125	64	2
3	100	88	1
4	125	65	1
5	64	63	1
6	65	66	1
8	10	61	2
8	61	10	2
9	61	19	1
10	61	30	1
12	10	94	1
16	94	60	1

val	grp	row_id	depth
10	3	8	0
19	3	9	1
30	3	10	1
60	3	16	2
61	3	8	0
63	2	5	1
64	2	2	0
65	2	4	1
66	2	6	2
88	1	3	1
94	3	12	1
100	1	1	0
114	1	1	0
125	2	2	0

row_id	old_val	new_val	link_count
1	100	114	2
1	114	100	2
2	64	125	2
2	125	64	2
3	100	88	1
4	125	65	1
5	64	63	1
6	65	66	1
8	10	61	2
8	61	10	2
9	61	19	1
10	61	30	1
12	10	94	1
16	94	60	1

val	grp	row_id	depth
10	3	8	0
19	3	9	1
30	3	10	1
60	3	16	2
61	3	8	0
63	2	5	1
64	2	2	0
65	2	4	1
66	2	6	2
88	1	3	1
94	3	12	1
100	1	1	0
114	1	1	0
125	2	2	0

row_id	old_val	new_val	link_count
1	100	114	2
1	114	100	2
2	64	125	2
2	125	64	2
3	100	88	1
4	125	65	1
5	64	63	1
6	65	66	1
8	10	61	2
8	61	10	2
9	61	19	1
10	61	30	1
12	10	94	1
16	94	60	1

val	grp	row_id	depth
10	3	8	0
19	3	9	1
30	3	10	1
60	3	16	2
61	3	8	0
63	2	5	1
64	2	2	0
65	2	4	1
66	2	6	2
88	1	3	1
94	3	12	1
100	1	1	0
114	1	1	0
125	2	2	0