Simple CTE Hierarchy question - 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)

Simple CTE Hierarchy question

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-08-05 : 09:49:48

Hi Guru's,

My Table:

ID      NAME    PARENTID
1	A	0
2	AA	1
3	B	0
4	AAA	2
5	AA	1
6	AAAA	4
7	AA	1
8	ABCDEF	7

My Query:

with Recursion (id, name, parentid, path, Level)
as
(
select	id,
	Name,
	ParentID,
	convert(nvarchar(32), '0') as path,
	0
from
	Menu h
where
	h.ParentID = 0
union all
select	h.id,
	h.Name,
	h.ParentID,
	convert(nvarchar(32), r.path + '.' + convert(varchar(10), h.id)) as path,
	r.Level + 1
from
	Menu h

	inner join Recursion as r
	on h.ParentID = r.id
)

select id, name, ParentID, path, level from Recursion ORDER BY Path

My Result:

1	A	0	0	0
3	B	0	0	0
2	AA	1	0.2	1
4	AAA	2	0.2.4	2
6	AAAA	4	0.2.4.6	3
5	AA	1	0.5	1
7	AA	1	0.7	1
8	ABCDEF	7	0.7.8	2

But this is incorrect as ID 2 AA is a child of A. Is this because I haev 2 root nodes? i.e. A & B? How do I correct this. I could use a fake node but that isn't as clean as I would like.

Thanks as always

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 10:52:00

What else can ORDER BY Path do?

Webfred

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-08-05 : 10:55:17

A fake root works well. Is it possible to have multiple root nodes in a CTE hierarchy?

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-08-05 : 10:55:59

Sorry webfred, didn't see you post. Thanks for replying...but you got me :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 11:35:56

Is this better?


set nocount on
declare @menu table (id int, name varchar(12), parentid int)
insert @menu
select 1,	'A'	,null union all
select 2,	'AA'	,1 union all
select 3,	'B'	,null union all
select 4,	'AAA'	,2 union all
select 5,	'AA'	,1 union all
select 6,	'AAAA'	,4 union all
select 7,	'AA'	,1 union all
select 8,	'ABCDEF'	,7


;with Recursion (id, name, parentid, path, Level)
as
(
select	id,
	Name,
	ParentID,
	convert(nvarchar(32), h.id) as path,
	0
from
	@Menu h
where
	h.ParentID is null
union all
select	h.id,
	h.Name,
	h.ParentID,
	convert(nvarchar(32), r.path + '.' + convert(varchar(10), h.id)) as path,
	r.Level + 1
from
	@Menu h

	inner join Recursion as r
	on h.ParentID = r.id
)

select id, name, ParentID, path, level from Recursion ORDER BY Path

output:
id          name         ParentID    path                             level
----------- ------------ ----------- -------------------------------- -----------
1           A            NULL        1                                0
2           AA           1           1.2                              1
4           AAA          2           1.2.4                            2
6           AAAA         4           1.2.4.6                          3
5           AA           1           1.5                              1
7           AA           1           1.7                              1
8           ABCDEF       7           1.7.8                            2
3           B            NULL        3                                0

Be One with the Optimizer
TG

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-08-05 : 11:47:42

Yes sir it is, Sir!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 12:49:02

Yes TG - that is what i meant!
Both roots A and B cannot start with '0' in there path.

Webfred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources