April 5, 2009 at 7:47 am
Hi,
I have an original table "MyTable" with nodes like followings
MyTable's data
NodeID ParentNodeID Name Level
------- ------------- ------ -----
1 null Root 0
2 1 A 1
3 1 B 1
4 2 C 2
5 2 D 2
6 3 E 2
7 3 F 2
8 4 G 3
9 4 H 3
10 4 I 3
11 5 J 3
I would like to generate a temporary table #tempTable from MyTable with nodes respected to their node parent and ancestors as following:
#tempTable
NodeID ParentNodeID Name Level
------- ------------- ------ -----
1 null Root 0
2 1 A 1
4 2 C 2
8 4 G 3
9 4 H 3
10 4 I 3
5 2 D 2
11 5 J 3
3 1 B 1
6 3 E 2
7 3 F 2
Thanks in advance.
April 5, 2009 at 9:16 pm
Hi,
Show you’re schema of the mytable
And
Which statement you’re using?
A) select * into temp from mytable
or
B) insert into temp
select * from mytable
ARUN SAS
🙂
April 5, 2009 at 11:05 pm
Hi,
Use a recursive cte to build up your structure. The [sort] column is only used to store the relationships (and therefore the sorting).
create table #temp(NodeID int, ParentNodeID int null, [Name] varchar(8), [Level] int)
insert #temp
select 1, null, 'Root', 0
union all select 2, 1, 'A', 1
union all select 3, 1, 'B', 1
union all select 4, 2, 'C', 2
union all select 5, 2, 'D', 2
union all select 6, 3, 'E', 2
union all select 7, 3, 'F', 2
union all select 8, 4, 'G', 3
union all select 9, 4, 'H', 3
union all select 10, 4, 'I', 3
union all select 11, 5, 'J', 3
go
with cte(NodeID, ParentNodeID , [Name] , [Level], [sort]) as
(select *, CAST(a.NodeID AS varchar) as [sort] from #temp a where ParentNodeID is null
union all
select a.*, CAST(b.sort + CAST (a.NodeID AS varchar) AS varchar) as [sort] from #temp a inner join cte b on a.ParentNodeID = b.NodeID
)
select NodeID, ParentNodeID , [Name] , [Level] into #tempTable from cte order by sort
select * from #temptable
Bevan
April 6, 2009 at 9:22 am
johnsql (4/5/2009)
Hi,I have an original table "MyTable" with nodes like followings
MyTable's data
NodeID ParentNodeID Name Level
------- ------------- ------ -----
1 null Root 0
2 1 A 1
3 1 B 1
4 2 C 2
5 2 D 2
6 3 E 2
7 3 F 2
8 4 G 3
9 4 H 3
10 4 I 3
11 5 J 3
I would like to generate a temporary table #tempTable from MyTable with nodes respected to their node parent and ancestors as following:
#tempTable
NodeID ParentNodeID Name Level
------- ------------- ------ -----
1 null Root 0
2 1 A 1
4 2 C 2
8 4 G 3
9 4 H 3
10 4 I 3
5 2 D 2
11 5 J 3
3 1 B 1
6 3 E 2
7 3 F 2
Thanks in advance.
Could any one please tell me what is the difference between the outputs apart from Sorting?
April 6, 2009 at 2:49 pm
The first list is sorted by nodeID - the second list starts with the parent (the one with no parentID listed) and then lists the child, then it's children (grandchildren) etc, then the next child, it's children, etc.
It could be an organisational structure or some other multi level list.
Bevan
April 6, 2009 at 9:09 pm
Bevan keighley (4/6/2009)
The first list is sorted by nodeID - the second list starts with the parent (the one with no parentID listed) and then lists the child, then it's children (grandchildren) etc, then the next child, it's children, etc.It could be an organisational structure or some other multi level list.
Bevan
Bevan,
Thank you very much for your help. What you think is exactly what I expect for the result. The expectation is the root node is listed first, then it first child (level of 1), then display all ancestors of this first child (nodeID of 2) until no more its ancestors. Then the second child of the root (nodeID of 3 at level of 1), and then display all ancestors of this first child (nodeID of 3) until no more its ancestors. The pattern of display for other nested nodes at other levels >= 2 of the tree has the same idea ...
Thanks again.
johnsql
April 7, 2009 at 2:13 am
Hey there,
You probably already know, but when you move to SS2K8, check out the new hierarchy id type.
See http://qa.sqlservercentral.com/articles/SQL+Server+2008/62204/
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply