By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table RelData
(
ParentId int,
Id int,
Name nvarchar(3)
);
insert into RelData (ParentId, Id, Name) values
(null, 1, 'A00'), -- tree A
(1, 2, 'A10'),
(2, 3, 'A11'),
(2, 4, 'A12'),
(1, 5, 'A20'),
(5, 6, 'A21'),
(null, 7, 'B00'), -- tree B
(7, 8, 'B10'),
(8, 9, 'B11');
select * from RelData;
| ParentId | Id | Name |
|---|---|---|
| null | 1 | A00 |
| 1 | 2 | A10 |
| 2 | 3 | A11 |
| 2 | 4 | A12 |
| 1 | 5 | A20 |
| 5 | 6 | A21 |
| null | 7 | B00 |
| 7 | 8 | B10 |
| 8 | 9 | B11 |
-- reproduce current situation
WITH cte_Rel AS (
SELECT rd.Id, rd.Name, rd.ParentId, convert(nvarchar(3), null) as ParentName
FROM RelData rd
WHERE rd.ParentId is null
UNION ALL
SELECT rd.Id, rd.Name, rd.ParentId, c.name
FROM RelData rd
JOIN cte_Rel c ON rd.ParentId = c.Id
)
SELECT c.ParentId, c.ParentName, c.Name
FROM cte_Rel c
where c.ParentId is not null
order by c.ParentId;
| ParentId | ParentName | Name |
|---|---|---|
| 1 | A00 | A10 |
| 1 | A00 | A20 |
| 2 | A10 | A11 |
| 2 | A10 | A12 |
| 5 | A20 | A21 |
| 7 | B00 | B10 |
| 8 | B10 | B11 |
-- number different trees
WITH cte_Rel AS (
SELECT row_number() over(order by rd.Id) * 100 as TreeId, -- number to roots and multiply the root number by 100
rd.Id, rd.Name, rd.ParentId, convert(nvarchar(3), null) as ParentName
FROM RelData rd
WHERE rd.ParentId is null
UNION ALL
SELECT c.TreeId, -- repeat the tree number
rd.Id, rd.Name, rd.ParentId, c.name
FROM RelData rd
JOIN cte_Rel c ON rd.ParentId = c.Id
)
SELECT c.TreeId, c.ParentId, c.ParentName, c.Name
FROM cte_Rel c
where c.ParentId is not null
order by c.ParentId;
| TreeId | ParentId | ParentName | Name |
|---|---|---|---|
| 100 | 1 | A00 | A10 |
| 100 | 1 | A00 | A20 |
| 100 | 2 | A10 | A11 |
| 100 | 2 | A10 | A12 |
| 100 | 5 | A20 | A21 |
| 200 | 7 | B00 | B10 |
| 200 | 8 | B10 | B11 |