Hierarchical Rollup Help (SQL Server 2005)
-
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int. The table is setup as follows: Parent Child Pop ---------------------------------- Europe France 0 France Paris 1 New York New York City 10 North America United States 0 North America Canada 0 United States New York 0 United States Washington 0 Washington Redmond 200 Washington Seattle 100 World Europe 0 World North America 0 This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren. I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too): World Null 311 World Europe 1 Europe France 1 France Paris 1 World North America 310 North America United States 310 North America Canada 0 United States New York 10 United States Washington 300 New York New York City 10 Washington Redmond 200 Washington Seattle 100 Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree. Any help/ideas would be appreciated! Thank you.
-
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int. The table is setup as follows: Parent Child Pop ---------------------------------- Europe France 0 France Paris 1 New York New York City 10 North America United States 0 North America Canada 0 United States New York 0 United States Washington 0 Washington Redmond 200 Washington Seattle 100 World Europe 0 World North America 0 This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren. I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too): World Null 311 World Europe 1 Europe France 1 France Paris 1 World North America 310 North America United States 310 North America Canada 0 United States New York 10 United States Washington 300 New York New York City 10 Washington Redmond 200 Washington Seattle 100 Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree. Any help/ideas would be appreciated! Thank you.
Make a function:
CREATE FUNCTION dbo.GetPopTotal(@cParent varchar(50))
RETURNS int AS
BEGIN
declare @nTotal int
declare @nGrandTotal int
declare @cChild varchar(50)set @nTotal = 0
DECLARE childcursor CURSOR FOR
SELECT DISTINCT child,pop
FROM travel
WHERE parent = @cParentOPEN childcursor
FETCH NEXT FROM childcursor INTO @cChild, @nGrandTotal
WHILE @@FETCH_STATUS = 0
BEGIN
select @nGrandTotal = @nGrandTotal + @nTotal + dbo.GetPopTotal(@cChild)FETCH NEXT FROM childcursor INTO @cChild, @nTotalEND
CLOSE childcursor
DEALLOCATE childcursorreturn isnull(@nGrandTotal,0)
END
Call it like this:
select parent,child, dbo.GetPopTotal(child) + pop as PopTotal from travel
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters