SQL Server function to get parent top level in hierarchy
I have the following table structure (master_group):
code name under
1 National Sales Manager 1
2 regional sales manager 1
3 area sales manager 2
4 sales manager 3
How to get the final parent of a specific string, like:
code name under ultimateparent
1 National Sales Manager 1 1
2 regional sales manager 1 1
3 area sales manager 2 1
4 sales manager 3 1
source to share
With a recursive cte going from top to children:
with cte as(
select *, code as ultimate from t where code = under
union all
select t.*, c.ultimate from t
join cte c on c.code = t.under
where t.code <> t.under
)
select * from cte
For data:
create table t (code int, name varchar(100), under int)
insert into t values
(1, 'National Sales Manager', 1),
(2, 'regional sales manager', 1),
(3, 'area sales manager', 2),
(4, 'sales manager', 3),
(5, 'a', 5),
(6, 'b', 5),
(7, 'c', 5),
(8, 'd', 7),
(9, 'e', 7),
(10, 'f', 9),
(11, 'g', 9)
it generates output:
code name under ultimate
1 National Sales Manager 1 1
5 a 5 5
6 b 5 5
7 c 5 5
8 d 7 5
9 e 7 5
10 f 9 5
11 g 9 5
2 regional sales manager 1 1
3 area sales manager 2 1
4 sales manager 3 1
source to share
You can use a recursive CTE to navigate to the tree and then select the highest level for each code:
with cte as (
select mg.code, mg.name as name, mg.under as under, mg.under as parent, 1 as lev
from master_group mg
union all
select mg.code, mg.name, mg.under, cte.under as parent, cte.lev + 1
from master_group mg join
cte
on mg.under = cte.code
where cte.under is not null and cte.under <> mg.code
)
select code, name, under, parent as ultimateparent
from (select cte.*, max(lev) over (partition by cte.code) as maxlev
from cte
) t
where lev = maxlev;
Here is the SQL script.
source to share
I would put NULL as under (in my example ParentId) when it is the top record. Under this assumption, here's the solution
;
WITH Result AS
(
SELECT Id, ParentId, Name, Id as [Top] FROM
sample
where ParentId IS NULL
UNION ALL
SELECT s.Id, s.ParentId, s.Name, [Top]
FROM sample s INNER JOIN Result R ON s.ParentId = R.Id
)
source to share
I suggest you use a recursive function like this:
CREATE FUNCTION dbo.parentID (@code int)
RETURNS int AS
BEGIN
DECLARE @ResultVar int
SELECT @ResultVar = (SELECT under FROM master_group WHERE code = @code)
IF @ResultVar <> @code
BEGIN
SELECT @ResultVar = dbo.parentID(@ResultVar)
END
RETURN @ResultVar
END
GO
Use it like this:
SELECT *,
dbo.parentId(code) AS ultimateparent
FROM master_group
source to share
I'm going to shamelessly steal data settings from another answer and demonstrate how you would do it using hierarchy:
create table t (code int, name varchar(100), under int)
insert into t values
(1, 'National Sales Manager', null),
(2, 'regional sales manager', 1),
(3, 'area sales manager', 2),
(4, 'sales manager', 3),
(5, 'a', null),
(6, 'b', 5),
(7, 'c', 5),
(8, 'd', 7),
(9, 'e', 7),
(10, 'f', 9),
(11, 'g', 9);
with cte as (
select code, name, under as parentCode, code as ultimateParent, cast('/' + cast(code as varchar) + '/' as nvarchar(max)) as h
from t
where under is null
union all
select child.code, child.name, child.under as ParentCode, parent.ultimateParentCode, cast(parent.h + cast(child.code as varchar) + '/' as nvarchar(max))
from t as child
join cte as parent
on child.under = parent.code
), hier as (
select code, name, parentCode, ultimateParentCode, cast(h as hierarchyid) as h
from cte
)
select code, name, parentCode, ultimateParentCode, h.ToString(), h.GetAncestor(h.GetLevel()-1).ToString()
from hier
Keep in mind that a recursive CTE only needs to be done once (or when data changes). What I am doing is that once you select the hierarchy (which you can store in a string, by the way), it is easy to answer the question you are asking with method calls on the hierarchy (and possibly a join if you want to get information about the predecessor).
source to share