Recursive call on table

Question

I have a table like this:

declare @departments table
(
depa_key int,
depa_key1 int
)

insert into @departments(depa_key, depa_key1)
select 1,2
union all
select 2,4
union all
select 4,5

So, after filling some data, table looks like this:

depa_key    depa_key1
1               2
2               4
4               5
5              NULL

DEPA_KEY1 is always a parent from `DEPA_KEY.

What I would like to do is get whole hierachy (all depa_key1) for given depa_key.

Example: For depa_key = 1 result should be:

depa_key
2
4
5

But I don't know how to do this?

Answer 1

You can use a recursive CTE:

;with cte as (
    select depa_key1 from @departments where depa_key = 1
    union all
    select d.depa_key1 from cte c join @departments d on c.depa_key1 = d.depa_key
)
select * from cte;

Demo