T-SQL employee hierarchy recursive query

Question

I would like to use a T-SQL query (it can be recursive CTE or anything) where I could get the following output as highlighted

SQL to create sample table as follows

--drop table #hierarchy
CREATE TABLE #hierarchy
(
    ID INTEGER NOT NULL PRIMARY KEY,
    Value CHAR(10) NOT NULL,
);

INSERT INTO #hierarchy
VALUES (1, 'a1'), (2, 'b2'), (3, 'c3'), (4, 'd4'),
       (5, 'e5'), (6, 'f6'), (7, 'g7'), (8, 'h8');

Any suggestions welcome, have tried recursive cte similar to factorial manipulation, however would appreciate inputs.

Thanks in advance.

user_10676716 · Answer

You seem to be looking for string concatenation:

select
    id,
    value,
    (
        select string_agg(trim(value), '->') within group(order by id)
        from #hierarchy h1
        where h1.id <= h.id
    ) path
from #hierarchy h

Demo on DB Fiddle:

id | value      | path

-: | :--------- | :-----------------------------
 1 | a1         | a1

 2 | b2         | a1->b2

 3 | c3         | a1->b2->c3

 4 | d4         | a1->b2->c3->d4

 5 | e5         | a1->b2->c3->d4->e5

 6 | f6         | a1->b2->c3->d4->e5->f6

 7 | g7         | a1->b2->c3->d4->e5->f6->g7

 8 | h8         | a1->b2->c3->d4->e5->f6->g7->h8

In versions of SQL Server that do not support `string_agg(), you would go:

select
    id,
    value,
    stuff(
        (
            select distinct '->' + trim(h1.value) val
            from #hierarchy h1
            where h1.id <= h.id
            order by val
            for xml path(''), type
        ).value('.', 'nvarchar(max)')
        , 1, 2, ''
    ) path
from #hierarchy h

Demo

user_1144035 · Answer

You seem to want a cumulative string aggregation:

select string_agg(value, '->') over (order by id)
from hierarchy

But SQL Server doesn't support this. Instead, you can use cross apply:

select h.*, h2.str
from hierarchy h cross apply
     (select string_agg(trim(value), '->') within group (order by id) as str
      from hierarchy h2
      where h2.id <= h.id
     ) h2

Here is a db<>fiddle.

If your names are non-overlapping, it might be more convenient to do the string concatenation once and then fetch the portions you want. For your example data, this works:

select h.*, s.str, 
       left(s.str, patindex('%' + trim(h.value) + '%', s.str) + len(trim(h.value)) - 1)
from hierarchy h cross join
     (select string_agg(trim(value), '->') within group (order by id) as str
      from hierarchy
     ) s;

(And this could be made to work with overlapping strings, but the logic is a bit more cumbersome.)

You can also use recursive CTEs:

with cte as (
      select h.id, trim(h.value) as value, convert(varchar(max), trim(h.value)) as str
      from hierarchy h
      where id = 1
      union all
      select h.id, trim(h.value) as value, cte.str + '->' + trim(h.value) as str
      from cte join
           hierarchy h
           on h.id = cte.id + 1
     )
select *
from cte;

This uses the fact that the ids are sequential and have no gaps. If that is not guaranteed, you can use row_number() to generate such a number.

T-SQL employee hierarchy recursive query

2 Answers