SQL query for hierarchical number

Question

Here is the data:

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);

And the result i want:

ID|Name|HierarchicalNumber
1 |   A|   1
2 |   B|   1.2
3 |   C|   1.3
4 |   D|   1.2.1

The clue is that each entry has own order number, but the query should returns hierarchical number which contains all the parent numbers as a subsequence delimited by the point with the own number at the end. The main feature requests an ability of providing a hierarchical number for any entry, so the query should be able to receive an ID of the entry and to return a hierarchical number of this. DBMS is MSSQL 2017. Thanks in advance!

Answer 1

You want a recursive CTE

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
  )
select *
from h
order by hid;

It can be refactored as a TVF as well

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
           -- stop when @id is found
           and h.id != @id
      )
    select *
    from h
    where id = @id;

db<>fiddle