Generate hierarchical structure in sql server

Question

I have a table employees

id	name	manager
1	susan	null
2	john	1
3	mary	1
4	josh	3
5	mathew	4
6	leo	2
7	peter	5

I want an output like:

id	name	hierarchy
1	susan	null
2	john	susan->john
3	mary	susan->mary
4	josh	susan->mary->josh
5	mathew	susan->mary->josh->mathew
6	leo	susan->john->leo
7	peter	susan->mary->josh->mathew->peter

DDL:

create table employees(id int,name varchar(10), manager int);
insert into employees
values(1,'susan',null)
,(2,'john',1)
,(3,'mary',1)
,(4,'josh',3)
,(5,'mathew',4)
,(6,'leo',2)
,(7,'peter',5)

Answer 1

You can use a recursive CTE for this.

WITH cte
AS (SELECT
          e.id,
          e.name,
          CAST(e.name AS VARCHAR(MAX)) AS hierarchy,
          e.manager
    FROM  dbo.employees AS e
    WHERE e.manager IS NULL
    UNION ALL
    SELECT
         e.id,
         e.name,
         CAST(ISNULL(c.hierarchy, '') + '->' + ISNULL(e.name, '') AS VARCHAR(MAX)),
         e.manager
    FROM cte AS c
         JOIN dbo.employees AS e ON e.manager = c.id)
SELECT cte.id,
       cte.name,
       CASE WHEN cte.manager IS NOT NULL THEN cte.hierarchy ELSE NULL END AS hierarchy
FROM   cte
OPTION(MAXRECURSION 0)

When you have more then 100 iterations, which your example has not, you need to provide an option: OPTION(MAXRECURSION 0)