SQL query for hierarchical number-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Looks like a plain recursive query, in which you build the hierarchy string as you go from the contents of whatever you already have in a parent row of a new row you're fetching - as a continuous string concatenation.

Like here below - or are you looking for something else?

WITH rec AS (
  SELECT
    id  
  , name
  , CAST(id AS VARCHAR(16)) AS hierarchicalnumber
  FROM correspondents
  WHERE parentid IS NULL
  UNION ALL 
  SELECT
    correspondents.id
  , correspondents.name
  ,   rec.hierarchicalnumber 
      '.' 
      CAST(correspondents.id AS VARCHAR(16)) AS hierarchialnumber                                                                                                          
  FROM correspondents JOIN rec ON rec.id = correspondents.parentid
)
SELECT * FROM rec;
-- out  id | name | hierarchicalnumber 
-- out ---- ------ --------------------
-- out   1 | A    | 1
-- out   2 | B    | 1.2
-- out   3 | C    | 1.3
-- out   4 | D    | 1.2.4