Hierarchical update query for the tree structure data in SQL

create table #tmp(ChildID nvarchar(50),ParentID nvarchar(50), Percentage numeric(8,2))
insert into #tmp values ('1', NULL,NULL)
insert into #tmp values ('1.1', '1',89)
insert into #tmp values ('1.2', '1',NULL)
insert into #tmp values ('1.2.1','1.2',96)
insert into #tmp values ('1.2.2', '1.2',NULL)
insert into #tmp values ('1.2.2.1', '1.2.2',87)
insert into #tmp values ('1.2.2.2', '1.2.2',NULL)
insert into #tmp values ('1.2.2.2.1', '1.2.2.2',87)


I am trying CTE update but couldn't attack the expected result yet. Can some one help in this.

CTE

You have just given the input and expected output. Can you also add some explanation of what you are actually trying to accomplish?
– Giorgos Betsos
2 days ago

3 Answers
3

I think this is what you want:

With cte as
(Select ChildId, Sum(Percentage) as [percent] from (
Select t1.ChildID,t1.Percentage as oldPercent, t2.ParentID, t2.Percentage from #tmp t1
join #tmp t2 on t2.ParentID like t1.ChildID +'%')c
group by ChildId
)
Update #tmp
set Percentage = cte.[percent]
from #tmp join cte
on #tmp.ChildID = cte.ChildID


Have a look at this.

DECLARE @tmp TABLE (ChildID NVARCHAR(50)
,ParentID NVARCHAR(50)
,Percentage NUMERIC(8, 2)
)
INSERT INTO @tmp VALUES ('1', NULL, NULL)
INSERT INTO @tmp VALUES ('1.1', '1', 89)
INSERT INTO @tmp VALUES ('1.2', '1', NULL)
INSERT INTO @tmp VALUES ('1.2.1', '1.2', 96)
INSERT INTO @tmp VALUES ('1.2.2', '1.2', NULL)
INSERT INTO @tmp VALUES ('1.2.2.1', '1.2.2', 57)
INSERT INTO @tmp VALUES ('1.2.2.2', '1.2.2', NULL)
INSERT INTO @tmp VALUES ('1.2.2.2.1', '1.2.2.2', 62);

WITH cteTree
AS (SELECT t1.ChildID, t1.childid AS OriginalID
, t1.Percentage
FROM @tmp t1
UNION ALL
SELECT t2.ChildID
, c.OriginalID
, t2.Percentage
FROM @tmp t2
JOIN cteTree c ON t2.parentID = c.ChildID
)

SELECT OriginalID AS ChildID, SUM(cteTree.Percentage)
FROM cteTree
GROUP BY OriginalID
ORDER BY cteTree.OriginalID


The other guys were pretty close, but needs to iterate slightly differently, and iteration can be tricky if you don't use it a lot.

You can try this :

DECLARE @temp table(ChildID nvarchar(50),ParentID nvarchar(50), Percentage numeric(8,2))
insert into @temp values ('1', NULL,NULL)
insert into @temp values ('1.1', '1',89)
insert into @temp values ('1.2', '1',NULL)
insert into @temp values ('1.2.1','1.2',96)
insert into @temp values ('1.2.2', '1.2',NULL)
insert into @temp values ('1.2.2.1', '1.2.2',57)
insert into @temp values ('1.2.2.2', '1.2.2',NULL)
insert into @temp values ('1.2.2.2.1', '1.2.2.2',62)

;With cte
AS
(
select * ,ROW_NUMBER()Over(Order by (select NULL))Rn from @temp
)
Select
ChildID
,ParentID
,Case when Percentage IS NULL Then (select SUM(c.Percentage) From Cte c Where c.Rn>cte.Rn) Else Percentage END Percentage
from cte


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.