HIERARCHY HELP! PLEASE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HIERARCHY HELP! PLEASE?

Author

Topic

johnnyrocket5
Starting Member

4 Posts

Posted - 2008-02-26 : 17:24:01

I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:

Net Profit
.....Gross Profit
..........Revenue
..........Direct Costs
.....Indirect Expense

Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:

Expenses
.....Node a
..........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Net Sales Adjustments
.....Child a (Revenue type)
.....Child b (Revenue type)

Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:

Net Profit
....Gross Profit
........Revenue
................Net Sales Adjustments
....................Child a (Revenue type)
....................Child b (Revenue type)
........Direct Costs
...........Expenses
.................Child b (Direct Cost type)
........Indirect Expense
............Expenses
.................Node a
.....................Child a (Indirect Expense Type)

Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:

Expenses
.....Node a
...........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)

Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.

I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.

Thanks in advance for your time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 17:41:05

There are examples both here on SQLTeam and Books Online how to use CTE for this.

E 12°55'05.25"
N 56°04'39.16"

johnnyrocket5
Starting Member

4 Posts

Posted - 2008-02-26 : 17:43:18

1. Don't know what CTE is - If I did, I wouldn't have posted
2. I've found some examples, but they don't exactly address my problem

Thanks for the post...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 17:48:52

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-26 : 23:45:28

hi,

try with this

DECLARE @Sample TABLE (ID INT, ParentID INT, Name VARCHAR(1000))

INSERT @Sample
SELECT 7, NULL, 'Ljunggren' UNION ALL
SELECT 3, 1, 'Gulli' UNION ALL
SELECT 8, 7, 'Kerstin' UNION ALL
SELECT 1, NULL, 'Rosberg' UNION ALL
SELECT 4, 2, 'Peter' UNION ALL
SELECT 5, 3, 'Susanne' UNION ALL
SELECT 2, 1, 'Jan-Eric' UNION ALL
SELECT 10, 9, 'Jennie' UNION ALL
SELECT 6, 3, 'Annelie' UNION ALL
SELECT 9, 7, 'Kenneth' UNION ALL
SELECT 11, 9, 'Jessica'

;WITH Yak (ID, ParentID, Name, Path, Indent)
AS (
SELECT ID, ParentID,Name, CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)), 0
FROM @Sample
WHERE ParentID IS NULL

UNION ALL

SELECT s.ID,s.ParentID,s.Name, CONVERT(VARCHAR, y.Path + ',' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ID = s.ParentID
)

SELECT ID,
ParentID,
REPLICATE('.....', Indent) + Name,
Path
FROM Yak
ORDER BY Path

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 01:57:28

Post link instead http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88675
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89335
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89365

It makes it easier to understand the solution.

E 12°55'05.25"
N 56°04'39.16"

johnnyrocket5
Starting Member

4 Posts

Posted - 2008-03-19 : 15:13:39

Thanks to all for your help on this one - it worked like a charm!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources