How to get top dept_id of every row

Question

Question: How to get top dept_id of every row.

Input data

+---------+------------+----------+
| Dept_ID | PR_Dept_ID | DeptName |
+---------+------------+----------+
|    0000 | null       | HR00     |
|    0001 | 0000       | HR01     |
|    0002 | 0000       | HR02     |
|    0003 | 0002       | HR03     |
|    0004 | 0002       | HR04     |
|    0005 | 0004       | HR05     |
|    0006 | null       | INV00    |
|    0007 | 0006       | INV01    |
|    0008 | 0007       | INV02    |
+---------+------------+----------+

Tree level

HR00
    HR01
    HR02
        HR03
        HR04
            HR05
INV00
    INV01
        INV02

Expected result:

+---------+------------+----------+-------------+
| Dept_ID | PR_Dept_ID | DeptName | top_Dept_ID |
+---------+------------+----------+-------------+
|    0000 | null       | HR00     | null        |
|    0001 | 0000       | HR01     | 0000        |
|    0002 | 0000       | HR02     | 0000        |
|    0003 | 0002       | HR03     | 0000        |
|    0004 | 0002       | HR04     | 0000        |
|    0005 | 0004       | HR05     | 0000        |
|    0006 | null       | INV00    | null        |
|    0007 | 0006       | INV01    | 0006        |
|    0008 | 0007       | INV02    | 0006        |
+---------+------------+----------+-------------+

What I've tried & faced problem:

If row data's level is not changed then I can use join * level count to solve the problem. But now it's dynamic level, e.g. HR level is 4, INV level is 3, and I have trouble at here.

I would appreciate it if someone could give me a keyword or logic.

Online test link: SQL Server 2017 | db<>fiddle

You can try this [https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy/18111876#18111876](https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy/18111876#18111876) — Basin, Aug 31 '20 at 03:37
Keyword: Recursive CTE. Question, why are you using varchar for your parent id column? — Dale K, Aug 31 '20 at 03:41
@Dale K , HI, it's a demo , so the type could be uid or int in real world — Wei Lin, Aug 31 '20 at 03:51
Recursive CTE will create more rows,this is not match the goal in this case — Wei Lin, Aug 31 '20 at 03:53
@Wei you've done it yourself - good job! (you can see how it will simplify things having int instead of varchar). — Dale K, Aug 31 '20 at 04:22

score 2 · Accepted Answer · edited Aug 31 '20 at 04:18

Following Dale K & Basin keyword CTE Recursion to get tree hierarchy, I got the point and do some fixed to this case.

;with cte as (
    select *, cast(dept_id as varchar(255)) top_dept_id
    from dep
    where pr_dept_id is null
    union all
    select t1.*, t2.top_dept_id 
    from dep t1
    inner join cte t2 on t2.dept_id = t1.pr_dept_id 
)
select  
    dept_id, pr_dept_id, deptname
    , case when top_dept_id = cast(dept_id as varchar(255)) then null else top_dept_id end as top_dept_id
from cte
order by dept_id

SQL Server 2017 | db<>fiddle

How to get top dept_id of every row

1 Answers1