By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Correspondents
(
ID smallint NOT NULL,
ParentID smallint NULL,
Name nvarchar(30) NOT NULL,
OrderNumber int NOT NULL
);
INSERT INTO dbo.Correspondents VALUES
(1, null, 'A', 1),
(2, 1, 'B', 2),
(3, 1, 'C', 3),
(4, 2, 'D', 1);
4 rows affected
create function gethid(@id int) returns table
as return
with h as (
select *, cast(OrderNumber as varchar(max)) hid
from dbo.Correspondents
where ParentID is null
union all
select c.*, h.hid + '.' + cast(c.OrderNumber as varchar(10))
from dbo.Correspondents c
join h on h.id= c.ParentID and h.id != @id
)
select *
from h
where id = @id
select * from gethid(2)
| ID | ParentID | Name | OrderNumber | hid |
|---|---|---|---|---|
| 2 | 1 | B | 2 | 1.2 |