March 16, 2012 at 5:02 am
Hi...
I have a table (fctTable), which references another table (refTable) that has a parent/child relationship to it's self.
What i need to do is for each row in the fctTable i need to get the sum of a column on the refTable for whole tree of parents.
this is hard to explain so i have an example...
Here are my two tables:
fctTable
Id | refTableId
1 | 2
2 | 7
3 | 4
4 | 9
5 | 8
refTable
Id | ParentId | Value
1 | 0 | 17
2 | 1 | 11
3 | 0 | 23
4 | 5 | 5
5 | 3 | 7
7 | 2 | 13
8 | 0 | 3
9 | 8 | 19
and i need to get this out:
Result
fctTableId | (refTableIds) | SUM(refTable.Value) |
1 | 2,1 | 11 + 17 = 28
2 | 7,2,1 | 13 + 11 + 17 = 41
3 | 4,5,3 | 5 + 7 + 23 = 35
4 | 9,8 | 19 + 3 = 22
5 | 8 | 3 = 3
I don't need the refTableId's column in the results, thats just to help me work out what the heck i'm doing....
I can do it by creating a table valued function similar to the SP mentioned by Anish M here, which gets the tree for a given refTableId, and then calling that from a select statment that chooses the rows in the fctTable i want to use.
but that seems clunky and slow (i may have millions of records in the fctTable to do this for so slow ≡ bad)
I was wondering if any one can think of a better solution... any ideas?
March 16, 2012 at 5:50 am
You can use recursive CTEs for this
SET NOCOUNT ON
DECLARE @fctTable TABLE(Id INT,refTableId INT)
INSERT INTO @fctTable(Id,refTableId)
SELECT 1 , 2 UNION ALL
SELECT 2 , 7 UNION ALL
SELECT 3 , 4 UNION ALL
SELECT 4 , 9 UNION ALL
SELECT 5 , 8;
DECLARE @refTable TABLE(Id INT, ParentId INT, Value INT)
INSERT INTO @refTable(Id, ParentId, Value)
SELECT 1 , 0 , 17 UNION ALL
SELECT 2 , 1 , 11 UNION ALL
SELECT 3 , 0 , 23 UNION ALL
SELECT 4 , 5 , 5 UNION ALL
SELECT 5 , 3 , 7 UNION ALL
SELECT 7 , 2 , 13 UNION ALL
SELECT 8 , 0 , 3 UNION ALL
SELECT 9 , 8 , 19;
WITH Recur AS (
SELECT f.Id AS fctTableId,
r.Id AS refTableId,
r.Id,
r.ParentId,
r.Value AS SumValue
FROM @refTable r
INNER JOIN @fctTable f ON f.refTableId=r.Id
UNION ALL
SELECT r.fctTableId,
r.refTableId,
t.Id,
t.ParentId,
t.Value + r.SumValue
FROM @refTable t
INNER JOIN Recur r ON r.ParentId=t.Id
)
SELECT fctTableId,
SumValue
FROM Recur
WHERE ParentId=0
ORDER BY fctTableId;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537March 16, 2012 at 6:06 am
BOOM! Job done.
Thanks Mark!
(now. how does that work...)
March 16, 2012 at 6:35 am
Oh... be careful, now. That only works if each top level item only has a straight trunk instead of a tree. Run the code with the following data and see what I mean. I added a node to ParentID = 3.
SET NOCOUNT ON
DECLARE @fctTable TABLE(Id INT,refTableId INT)
INSERT INTO @fctTable(Id,refTableId)
SELECT 1 , 2 UNION ALL
SELECT 2 , 7 UNION ALL
SELECT 3 , 4 UNION ALL
SELECT 4 , 9 UNION ALL
SELECT 5 , 8;
DECLARE @refTable TABLE(Id INT, ParentId INT, Value INT)
INSERT INTO @refTable(Id, ParentId, Value)
SELECT 1 , 0 , 17 UNION ALL
SELECT 2 , 1 , 11 UNION ALL
SELECT 3 , 0 , 23 UNION ALL
SELECT 4 , 5 , 5 UNION ALL
SELECT 5 , 3 , 7 UNION ALL
SELECT 7 , 2 , 13 UNION ALL
SELECT 8 , 0 , 3 UNION ALL
SELECT 9 , 8 , 19 UNION ALL
SELECT 10, 3 , 100 --Added
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2012 at 8:03 am
oh yes, good spot jeff, but that is my fault for not being clear; I probably shouldn't have said "whole".
What I really needed to sum is the route from root to leaf...
The tree whose root is refTableId = 3 now looks something like this:
. .4
. . .10 . 5
.\ . /
. .3
so for refTableId = 5 i want the sum for the refTable records 5+3 (i.e. 7 + 23 = 30)
if it's 10 i would expect 10+3 (100 + 23 = 123)
and 4 would yeild 4+5+3 (5 + 7 + 23 = 35)
Mark's solution to gives me exactly that (whether by luck or design we shall have to leave for him to decide!)
Adding these records to fctTable:
SELECT 6 , 10 UNION ALL
SELECT 7 , 5;
i get:
128
241
335
422
53
6123
730
as expected (note the results for fctTableId 3, 6 and 7).
As an aside, Atif-ullah posted an alternate solution to my question on the microsoft forum...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy