May 16, 2011 at 12:32 pm
I have a table as such that is self joining with child and parent IDs. There can be infinite levels to this tree
Example:
ChildID, ParentID, Name, Count
NULL, 1, Top Level Tree 0
2, 1, Child1 2
3 , 1, Child2 1
4 , 2, Child1 of Child2 5
The child trees have items assigned to them but not necessarily the top level tree.
I need to write a query the returns the trees and the associated count along with the number of subtrees in that tree.
In the example above the results should be...
ID, Name, ItemCount, SubTreeCount
1,Top Level Tree,8,3 --Note: Top level should have sum and count of all children
2,Child1,7,1 --Note: Includes Child4 items as it is a child of it
3,Child2,1,0
4,Child of Child2,5,0
Any suggestions to accomplish this would be helpful.
I've tried using CTE but I can't use an aggregate in the recursive part where I basically need to sum up the children in order to get the correct total for the parent
Thanks
May 16, 2011 at 1:24 pm
How about some ddl and sample data along with desired output that matches the sample data? Please see the link in my signature about how to post questions to get the best results.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 3:06 pm
Here is some sample data for what I am trying to do.
--Create temp table for trees
create table #trees (ID int Identity(1,1), Parentid int NULL, TreeName varchar(20) NOT NULL,RootID int)
--Create tempo table for tree items
Create table #TreeItems (ID int identity(1,1),TreeID Int)
--Create Temp Table for results
Create table #results (Level int, ID int , ParentID int, TreeName varchar(20), ItemCnt int, SubTreeCnt int)
Declare @RootID int
Set @RootID = 1 --Top Level ID (Always known)
--Insert top level tree
Insert into #trees (ParentID, TreeName, RootID)
values (NULL,'Top Level Node',1)
--Insert First Level Child
Insert into #trees (ParentID, TreeName, RootID)
values (1,'Child1',1)
--Insert Grand Child
Insert into #trees (ParentID, TreeName, RootID)
values (2,'GChild1',1)
--Insert First Level Child(2)
Insert into #trees (ParentID, TreeName, RootID)
values (1,'Child2',1)
--Insert First Level Child(3)
Insert into #trees (ParentID, TreeName, RootID)
values (1,'Child3',1)
--Insert Grand Child
Insert into #trees (ParentID, TreeName, RootID)
values (5,'GChild2',1)
--Insert Tree Items
Insert Into #TreeItems (TreeID)
Values (2)
Insert Into #TreeItems (TreeID)
Values (2)
Insert Into #TreeItems (TreeID)
Values (4)
Insert Into #TreeItems (TreeID)
Values (4)
Insert Into #TreeItems (TreeID)
Values (4)
Insert Into #TreeItems (TreeID)
Values (5)
Insert Into #TreeItems (TreeID)
Values (6)
Insert Into #TreeItems (TreeID)
Values (6)
Insert Into #TreeItems (TreeID)
Values (6)
Insert Into #TreeItems (TreeID)
Values (6)
Insert Into #TreeItems (TreeID)
Values (3)
Insert Into #TreeItems (TreeID)
Values (3)
Insert Into #TreeItems (TreeID)
Values (3)
Insert Into #TreeItems (TreeID)
Values (3)
;With CTE(ID,Level,ParentID,TreeName)
AS (
Select t.ID,0 as Level,t.ParentID,TreeName
from #trees t
where t.ID=@RootID
Group by t.ID,t.TreeName,t.ParentID
union all
select t.ID,Level+1,t.ParentID,t.TreeName
from #trees t with(nolock)
inner join CTE b
ON t.ParentID =b.ID
)
Insert into #results (level, ID, ParentID, TreeName,Itemcnt)
Select CTE.level,CTE.ID,CTE.ParentID,CTE.TreeName, COUNT(b.treeid)
From CTE
left join #TreeItems b
on CTE.ID = b.TreeID
group by CTE.level,CTE.ID,CTE.ParentID,CTE.TreeName
Select * from #results
drop table #trees
drop table #treeitems
drop table #results
/*
The temp table #results returns this data.
LevelID,ParentID,TreeName, ItemCnt,SubTreeCnt
0,1,NULL, Top Level Node,0, NULL,
1,2,1, Child1, 2, NULL,
1,4,1, Child2, 3, NULL,
1,5,1, Child3, 1, NULL,
2,3,2, GChild1, 4, NULL,
2,6,5, GChild2, 4, NULL,
The results I am looking for is this...
Level,ID,ParentID,TreeName, ItemCnt,SubTreeCnt
0,1,NULL, Top Level Node, 14,3
1,2,1, Child1, 6,1
1,4,1, Child2, 3,0
1,5,1, Child3, 5,1
2,3,2, GChild1, 4,0
2,6,5, GChild2, 4,0
May 17, 2011 at 1:48 pm
Try this:
-- The CTE returns the cat hierarchy:
-- one row for each ancestor-descendant relationship
-- (including the self-relationship for each category)
WITH CTE
AS (
-- Anchor member: self relationship for each category
SELECT ID AS Ancestor,
ID AS Descendant,
0 AS Level
FROM #trees
UNION ALL
-- Recursive member: for each row, select the children
SELECT CTE.Ancestor,
t.ID,
Level + 1
FROM CTE
INNER JOIN #trees AS t ON CTE.Descendant = t.ParentID
)
SELECT CTE.Ancestor,
COUNT(TreeID) AS ProductsInTree,
MAX(CTE.Level) AS SubTreeCnt
-- outer join to product-categories to include
-- all categories, even those with no products directly associated
FROM CTE
LEFT JOIN #TreeItems PC ON CTE.Descendant = PC.TreeID
GROUP BY CTE.Ancestor
OPTION (MAXRECURSION 100) ;
Credit to answer by AakashM for the headstart: http://stackoverflow.com/questions/1003895/recursive-query-using-cte-in-sql-server-2005.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply