October 22, 2011 at 9:45 pm
declare @hierarchy table(id int,name nvarchar(40),parentid int,cost int)
insert into @hierarchy
select 1 as id,'a' as name , null as parentid , null as cost union all
select 2 as id,'b' as name , 1 as parentid , null as cost union all
select 3 as id,'c' as name , 2 as parentid , null as cost union all
select 4 as id,'d' as name , 3 as parentid , 100 as cost union all
select 5 as id,'e' as name , 3 as parentid , 200 as cost union all
select 6 as id,'f' as name , 3 as parentid , 400 as cost union all
select 10 as id,'g' as name , 2 as parentid , null as cost union all
select 11 as id,'h' as name , 10 as parentid , 50 as cost union all
select 12 as id,'i' as name , 10 as parentid , 50 as cost
declare @table table (id int,name nvarchar(40),parentid int,cost int,serial nvarchar(10))
;with cte
as
(
select ID,name,parentid,cost,CAST(id as varchar(max)) as serial from @hierarchy where parentid is null
union all
select a.id,a.name,a.parentid,a.cost,cte.serial +'.'+ cast(a.ID as varchar(max)) as serial from @hierarchy a
join cte on a.parentid=cte.id
)
insert into @table
select * from cte
--update @table
--set cost=b.newcost
--from @table a join dbo.total b
--on a.id=b.id
select * from @table
-- i tried to create this view and run update against this but it won't give me result for record 1 ,2
-- this need to recursively called.
--alter view total
--as
--select parentid as id,SUM(cost) as newcost from hierarchy group by parentid
-- required output
select 1 as id,'a' as name , null as parentid , 800 as cost,'1' as serial union all
select 2 as id,'b' as name , 1 as parentid , 800 as cost,'1.2' as serial union all
select 3 as id,'c' as name , 2 as parentid , 700 as cost,'1.2.3' as serial union all
select 10 as id,'g' as name , 2 as parentid , 100 as cost,'1.2.10' as serial union all
select 11 as id,'h' as name , 10 as parentid , 50 as cost,'1.2.10.11' as serial union all
select 12 as id,'i' as name , 10 as parentid , 50 as cost,'1.2.10.12' as serial union all
select 4 as id,'d' as name , 3 as parentid , 100 as cost,'1.2.3.4' as serial union all
select 5 as id,'e' as name , 3 as parentid , 200 as cost,'1.2.3.5' as serial union all
select 6 as id,'f' as name , 3 as parentid , 400 as cost,'1.2.3.6' as serial
please some one can it be solved with cte or do i need to use any other thing.
October 23, 2011 at 8:37 pm
First, thank you for providing the data in a readily consumable format. It helps a lot. 🙂
Here's your original data reformatted and put into a Temp Table just because I prefer working with Temp Tables instead of Table Variables. I didn't include any of the essential indexes because I don't know what you have for indexes already.
--===== Conditionally drop Temp Tables to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL DROP TABLE #Hierarchy;
IF OBJECT_ID('tempdb..#HierarchyNew','U') IS NOT NULL DROP TABLE #HierarchyNew;
GO
--===== Create the test table
CREATE TABLE #Hierarchy
(
ID INT,
Name NVARCHAR(40),
ParentID INT,
Cost INT
)
;
--===== Populate the test table with data provide by the OP
INSERT INTO #Hierarchy
(ID, Name, ParentID, Cost)
SELECT 1 AS ID, 'a' AS Name, NULL AS ParentID, NULL AS Cost UNION ALL
SELECT 2 AS ID, 'b' AS Name, 1 AS ParentID, NULL AS Cost UNION ALL
SELECT 3 AS ID, 'c' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL
SELECT 4 AS ID, 'd' AS Name, 3 AS ParentID, 100 AS Cost UNION ALL
SELECT 5 AS ID, 'e' AS Name, 3 AS ParentID, 200 AS Cost UNION ALL
SELECT 6 AS ID, 'f' AS Name, 3 AS ParentID, 400 AS Cost UNION ALL
SELECT 10 AS ID, 'g' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL
SELECT 11 AS ID, 'h' AS Name, 10 AS ParentID, 50 AS Cost UNION ALL
SELECT 12 AS ID, 'i' AS Name, 10 AS ParentID, 50 AS Cost
;
Here's the solution to your problem. It assumes you know a bit about Hierarchies and both what a Tally Table is and how it works to replace certain WHILE loops. If you don't know, then please see the article at the following link:
http://www.sqlservercentral.com/articles/T-SQL/62867/
Yes, the following code could be done all in one query... it's just not my nature to do so. I like to save the "interim" steps especially for troubleshooting.
--===== Build a "combined" table that will hold both the "Adjacency List"
-- and the near equivalent of the HierarchyID datatype but in 2005.
WITH cteBuildPath AS
( --=== This "anchor" section finds the top dog with a NULL as a parent
SELECT anchor.ID,
anchor.ParentID,
anchor.Name,
anchor.Cost,
CAST(CAST(anchor.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,
CAST(CAST(anchor.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,
1 AS HierarchyLevel
FROM #Hierarchy anchor
WHERE ParentID IS NULL
UNION ALL ---------------------------------------------------------------------------------------
--=== This "recusive" section puts the rest of the hierarchy together.
SELECT recur.ID,
recur.ParentID,
recur.Name,
recur.Cost,
CAST(rcte.HierarchyPath + CAST(recur.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,
CAST(rcte.HumanReadablePath + '.' + CAST(recur.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,
rcte.HierarchyLevel + 1 AS HierarchyLevel
FROM #Hierarchy recur
INNER JOIN cteBuildPath rcte
ON rcte.ID = recur.ParentID
) --=== This builds our new hierarchy table from the rCTE above.
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY HierarchyPath),0) AS SortOrder,
ID,
ParentID,
Name,
CAST(Cost AS BIGINT) AS Cost,
HierarchyPath,
HumanReadablePath,
HierarchyLevel
INTO #HierarchyNew
FROM cteBuildPath
;
--===== This little bit of computational heaven does what you want...
-- and could really do so much more!
WITH
cteSplit AS
( --=== Splits the path into elements so we can aggregate costs by ID
SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ID,
h.Cost AS Cost
FROM dbo.Tally AS t WITH(NOLOCK)
CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4
)
,
cteAggregate AS
( --=== Does the required totals by ID.
SELECT ID,
SUM(Cost) AS NodeDownLineTotalCost
FROM cteSplit
GROUP BY ID
) --=== Display the answer as requested.
SELECT hn.ID,
hn.Name,
hn.ParentID,
Cost = agg.NodeDownLineTotalCost,
Serial = hn.HumanReadablePath
FROM cteAggregate agg
INNER JOIN #HierarchyNew hn
ON agg.ID = hn.ID
WHERE agg.ID > 0
ORDER BY /*UGH!*/ Serial
;
Here's the output I get... I'd rather see it in the proper sort order but I did it your way instead...
ID Name ParentID Cost Serial
-- ---- -------- ---- ---------
1 a NULL 800 1
2 b 1 800 1.2
10 g 2 100 1.2.10
11 h 10 50 1.2.10.11
12 i 10 50 1.2.10.12
3 c 2 700 1.2.3
4 d 3 100 1.2.3.4
5 e 3 200 1.2.3.5
6 f 3 400 1.2.3.6
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2011 at 11:05 pm
Thanks you very much Jeff Moden for solving this and keeping the community alive by helping others.
regards,
October 24, 2011 at 4:35 am
My pleasure and thank you for the kudo.
Of more immediate concern is, have you studied the code, know how it works, or have any questions about how it works? I ask because I didn't have time to write very much about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2011 at 10:29 am
I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2011 at 10:59 am
Jack Corbett (10/24/2011)
I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?
It's a small "speed trick". Instead of the QP/QO doing the normal row to page to extent to table escalation, I just lock the whole table and prevent all of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2011 at 11:41 am
Jeff Moden (10/24/2011)
Jack Corbett (10/24/2011)
I have a question. Why the TABLOCKX hint on the temp table? What does it give you that the QP/QO wouldn't do anyway?It's a small "speed trick". Instead of the QP/QO doing the normal row to page to extent to table escalation, I just lock the whole table and prevent all of that.
That's what I thought, but I wanted some confirmation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 25, 2011 at 9:59 pm
Just the bold part i didn't get does it depend on number of levels or any other thing and the conversion of hierarchypath to binary why it is required?
WITH
cteSplit AS
( --=== Splits the path into elements so we can aggregate costs by ID
SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ID,
h.Cost AS Cost
FROM dbo.Tally AS t WITH(NOLOCK)
CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4
)
thanks in advance.
regards,
October 26, 2011 at 6:52 am
It splits the Hierarchy path back to ID's. Each ID can be a part of several "paths" and the only way to accumulate the total "cost" associated with each of those ID's is to sum the cost from whatever path they appear in. By their very nature, they are assocated with costs in the "downlines". By position, they are "level sensitive".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2011 at 8:38 pm
Just want to add my thanks to Jeff.
This article helped me solve my own, slightly similar problem (@ http://www.sqlservercentral.com/Forums/Topic1196820-391-1.aspx )
Mine is a bill of materials problem, and involved adding up nutritional information in raw materials (fat, protein, sugar, vitamins, minerals, etc), to find out what is in the finished product (or intermediate 'blends').
Once I'd fixed my problem, I decided to see if the technique I came up with would work for *this* problem; mostly just to satisfy my own curiosity.
So I thought I'd share it to demonstrate that there usually more than one way to solve a problem, and in the off-chance that someone else has a similar-but-not-quite-the-same problem that might benefit from this. The code below produces the same output as Jeff's, at least when given the same input (I haven't tested it with any other data).
--===== Conditionally drop Temp Tables to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL DROP TABLE #Hierarchy;
IF OBJECT_ID('tempdb..#HierarchyNew','U') IS NOT NULL DROP TABLE #HierarchyNew;
GO
--===== Create the test table
CREATE TABLE #Hierarchy
(
ID INT,
Name NVARCHAR(40),
ParentID INT,
Cost BIGINT
)
;
--===== Populate the test table with data provide by the OP
INSERT INTO #Hierarchy
(ID, Name, ParentID, Cost)
SELECT 1 AS ID, 'a' AS Name, NULL AS ParentID, NULL AS Cost UNION ALL
SELECT 2 AS ID, 'b' AS Name, 1 AS ParentID, NULL AS Cost UNION ALL
SELECT 3 AS ID, 'c' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL
SELECT 4 AS ID, 'd' AS Name, 3 AS ParentID, 100 AS Cost UNION ALL
SELECT 5 AS ID, 'e' AS Name, 3 AS ParentID, 200 AS Cost UNION ALL
SELECT 6 AS ID, 'f' AS Name, 3 AS ParentID, 400 AS Cost UNION ALL
SELECT 10 AS ID, 'g' AS Name, 2 AS ParentID, NULL AS Cost UNION ALL
SELECT 11 AS ID, 'h' AS Name, 10 AS ParentID, 50 AS Cost UNION ALL
SELECT 12 AS ID, 'i' AS Name, 10 AS ParentID, 50 AS Cost
;
--===== Build a "combined" table that will hold both the "Adjacency List"
-- and the near equivalent of the HierarchyID datatype but in 2005.
WITH cteBuildPath AS
( --=== This "anchor" section finds the top dog with a NULL as a parent
SELECT anchor.ID AS TopLevelID,
anchor.Name AS TopLevelName,
anchor.ID,
anchor.ParentID,
anchor.Name,
COALESCE(anchor.Cost,0) AS Cost,
CAST(CAST(anchor.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,
CAST(CAST(anchor.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,
1 AS HierarchyLevel
FROM #Hierarchy anchor
--WHERE ID IN (SELECT DISTINCT ParentID FROM #Hierarchy)
UNION ALL ---------------------------------------------------------------------------------------
--=== This "recusive" section puts the rest of the hierarchy together.
SELECT rcte.TopLevelID,
rcte.TopLevelName,
recur.ID,
recur.ParentID,
recur.Name,
rcte.Cost + COALESCE(recur.Cost,0),
CAST(rcte.HierarchyPath + CAST(recur.ID AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath,
CAST(rcte.HumanReadablePath + '.' + CAST(recur.ID AS VARCHAR(10)) AS VARCHAR(1000)) AS HumanReadablePath,
rcte.HierarchyLevel + 1 AS HierarchyLevel
FROM #Hierarchy recur
INNER JOIN cteBuildPath rcte
ON rcte.ID = recur.ParentID
) --=== This builds our new hierarchy table from the rCTE above.
SELECT TopLevelID,
TopLevelName,
ID,
ParentID,
Name,
Cost,
HierarchyPath,
HumanReadablePath,
HierarchyLevel
INTO #HierarchyNew
FROM cteBuildPath
;
SELECT * FROM #HierarchyNew;
-- sum up and display the totals for each top-level bom item
SELECT h.TopLevelID as id, h.TopLevelName as name, p.ParentID, SUM(h.Cost) AS Cost, p.HumanReadablePath AS Serial
FROM #HierarchyNew AS h
inner join #HierarchyNew p on h.TopLevelID = p.ID and p.TopLevelID = 1
group by h.TopLevelID, h.TopLevelName, p.ParentID, p.HumanReadablePath, p.HierarchyPath
order by p.HumanReadablePath
;
-- name, parent, cost, serial
select * from (
select 1 as id,'a' as name , null as parentid , 800 as cost,'1' as serial union all
select 2 as id,'b' as name , 1 as parentid , 800 as cost,'1.2' as serial union all
select 3 as id,'c' as name , 2 as parentid , 700 as cost,'1.2.3' as serial union all
select 10 as id,'g' as name , 2 as parentid , 100 as cost,'1.2.10' as serial union all
select 11 as id,'h' as name , 10 as parentid , 50 as cost,'1.2.10.11' as serial union all
select 12 as id,'i' as name , 10 as parentid , 50 as cost,'1.2.10.12' as serial union all
select 4 as id,'d' as name , 3 as parentid , 100 as cost,'1.2.3.4' as serial union all
select 5 as id,'e' as name , 3 as parentid , 200 as cost,'1.2.3.5' as serial union all
select 6 as id,'f' as name , 3 as parentid , 400 as cost,'1.2.3.6' as serial) x
order by serial
It works by treating each item in the hierarchy as its own top-level item, and then recursing down each resultant path to add the costs up for each 'top-level' item.
(Note: I COALESCE the costs to 0 in my query; since I have no SUM() that ignores NULL values, I have deal with them 'manually')
The devilish detail is lurking in the intermediate query results. I used this to work out what Jeff's code was doing (basically, the guts of the cteSplit query, with extra info):
SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS ApplyToID,
h.Cost AS Cost, h.ID, h.ParentID, h.Name, CAST(h.HierarchyPath as varbinary(20)) as HierarchyPath
FROM dbo.Tally AS t WITH(NOLOCK)
CROSS JOIN #HierarchyNew AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4
ORDER BY ApplyToID, ParentID, ID
it produces this:
ApplyToID Cost ID ParentID Name HierarchyPath
----------- -------------------- ----------- ----------- ---- ------------------------------------------
1 NULL 1 NULL a 0x00000001
1 NULL 2 1 b 0x0000000100000002
1 NULL 3 2 c 0x000000010000000200000003
1 NULL 10 2 g 0x00000001000000020000000A
1 100 4 3 d 0x00000001000000020000000300000004
1 200 5 3 e 0x00000001000000020000000300000005
1 400 6 3 f 0x00000001000000020000000300000006
1 50 11 10 h 0x00000001000000020000000A0000000B
1 50 12 10 i 0x00000001000000020000000A0000000C
2 NULL 2 1 b 0x0000000100000002
2 NULL 3 2 c 0x000000010000000200000003
2 NULL 10 2 g 0x00000001000000020000000A
2 100 4 3 d 0x00000001000000020000000300000004
2 200 5 3 e 0x00000001000000020000000300000005
2 400 6 3 f 0x00000001000000020000000300000006
2 50 11 10 h 0x00000001000000020000000A0000000B
2 50 12 10 i 0x00000001000000020000000A0000000C
3 NULL 3 2 c 0x000000010000000200000003
3 100 4 3 d 0x00000001000000020000000300000004
3 200 5 3 e 0x00000001000000020000000300000005
3 400 6 3 f 0x00000001000000020000000300000006
4 100 4 3 d 0x00000001000000020000000300000004
5 200 5 3 e 0x00000001000000020000000300000005
6 400 6 3 f 0x00000001000000020000000300000006
10 NULL 10 2 g 0x00000001000000020000000A
10 50 11 10 h 0x00000001000000020000000A0000000B
10 50 12 10 i 0x00000001000000020000000A0000000C
11 50 11 10 h 0x00000001000000020000000A0000000B
12 50 12 10 i 0x00000001000000020000000A0000000C
(29 row(s) affected)
compare this with my intermediate table:
SELECT h.TopLevelID AS ApplyToID, h.Cost AS Cost, h.ID, h.ParentID, h.Name, h.HierarchyPath
FROM #HierarchyNew h
ORDER BY h.TopLevelID, h.ParentID, h.ID
ApplyToID Cost ID ParentID Name HierarchyPath
----------- -------------------- ----------- ----------- ---- ------------------------------------------
1 0 1 NULL a 0x00000001
1 0 2 1 b 0x0000000100000002
1 0 3 2 c 0x000000010000000200000003
1 0 10 2 g 0x00000001000000020000000A
1 100 4 3 d 0x00000001000000020000000300000004
1 200 5 3 e 0x00000001000000020000000300000005
1 400 6 3 f 0x00000001000000020000000300000006
1 50 11 10 h 0x00000001000000020000000A0000000B
1 50 12 10 i 0x00000001000000020000000A0000000C
2 0 2 1 b 0x00000002
2 0 3 2 c 0x0000000200000003
2 0 10 2 g 0x000000020000000A
2 100 4 3 d 0x000000020000000300000004
2 200 5 3 e 0x000000020000000300000005
2 400 6 3 f 0x000000020000000300000006
2 50 11 10 h 0x000000020000000A0000000B
2 50 12 10 i 0x000000020000000A0000000C
3 0 3 2 c 0x00000003
3 100 4 3 d 0x0000000300000004
3 200 5 3 e 0x0000000300000005
3 400 6 3 f 0x0000000300000006
4 100 4 3 d 0x00000004
5 200 5 3 e 0x00000005
6 400 6 3 f 0x00000006
10 0 10 2 g 0x0000000A
10 50 11 10 h 0x0000000A0000000B
10 50 12 10 i 0x0000000A0000000C
11 50 11 10 h 0x0000000B
12 50 12 10 i 0x0000000C
(29 row(s) affected)
as a high school friend used to say: 'the same only different !'
In addition, the technique I used will still produce possibly useful results if you have a 'child' that has more than one 'parent'. e.g. if you add another row to the hierarchy:
INSERT INTO #Hierarchy (ID, Name, ParentID, Cost) VALUES (4, 'd', 10, 100);
you get this out:
ApplyToID name ParentID Cost Serial
----------- ---- ----------- -------------------- --------------------------------------------------
1 a NULL 900 1
2 b 1 900 1.2
10 g 2 200 1.2.10
11 h 10 50 1.2.10.11
12 i 10 50 1.2.10.12
4 d 10 200 1.2.10.4
3 c 2 700 1.2.3
4 d 3 200 1.2.3.4
5 e 3 200 1.2.3.5
6 f 3 400 1.2.3.6
(10 row(s) affected)
The upper-level 'summaries' are what I expect, but my query is coming 200 for both '4' items, instead of 100 (which is what I was expecting). Of course, depending on what you're reporting, that 200 may well be correct!
I'd like to continue on this, but I need to go and do something that'll pay my bills.
EDIT: (forgot to test before posting) that last result set is that same as you'd get from Jeff's code for the same input data.
regards
/Ryan
November 24, 2011 at 9:40 am
Apologies for the very late reply but thank you very much for posting all the work you did on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply