recursive menu thing - 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 2000 Forums

SQL Server Development (2000)

recursive menu thing

Author

Topic

Cyclonik
Posting Yak Master

114 Posts

Posted - 2001-12-14 : 21:04:41

i am using this format to build a heirarchal menu

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @i = @i + 1
INSERT #MenuTable
SELECT menu.item_id, sequence + right(convert(varchar(10),menu.item_id),10), @i + 1
FROM Menu, #MenuTable
WHERE #MenuTable.levelNo = @i
and menu.parent_id = #MenuTable.item_id and Menu.Active = 1
END

i need something like this out of it.. a variation on the sequence field
that looks like
1
1_1
1_1_1
1_2
1_2_1
1_2_1
2
2_1
2_1_1

if you get the picture any delimiter will suffice... . _ , whatever
any ideas?

i pull the data out of the temp table via:

SELECT a.item_ID,a.LevelNO, b.parent_id, a.sequence,
children_count=(select count(parent_id) from Menu where parent_id = a.item_id),
(DATALENGTH(a.sequence)-10) as depth, b.name as Name, b.link as Link, b.group_id
FROM #MenuTable a join menu b on a.item_id=b.item_id
WHERE a.item_id = menu.item_id ORDER by sequence

Christian

-=:SpasmatiK:=-

Edited by - Cyclonik on 12/14/2001 21:07:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources