Hierarchy routines

[Sammy145]

Hi guys I have a table (details below) now what i need

1.Create ID's (integers) for [Parent_group] and [sub_group] can some one school me on how to do this in vb.net .
2.The hierarchy can go to 5 levels deep.
3. There is a coloumn called Path can someone school me on how to loop though and populate this field -- example
'bath/bathsuba'

Im trying to create an xml sitemap and menu system.

CREATE TABLE [dbo].[Hierarchy6](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Parent_group] [varchar](12) NULL,
[sub_group] [varchar](12) NULL,
[description] [varchar](50) NULL,
[Sequence] [smallint] NULL,
[Level] [int] NULL,
Path varchar(5000) null

)
--DATA--
insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values (null,'bath','bath-stuff',10,0)

insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values (null,'clothes','clothes',20,0)

insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values (null,'zzz','test',30,0)


insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values ('bath','bathsuba','bath sub cat a',10,0)

insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values ('bathsuba','tester','bath sub sub cat a',10,0)

insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values ('clothes','clothe-ssubA','clothinga',10,0)

insert into hierarchy6(Parent_group,sub_group,description,sequence,level)
values ('zzz','zSubCat','clothes',20,0)

Thanks

Sam

 

[ousoonerjoe]

You do not need to loop through the records in the table to make this type of update. It is more practical and efficient to update an entire Set of records at one time, like I show below.

UPDATE Hierarchy6
SET [Path] = [Parent_Group] + '/' + [Sub_Group]
--No need for WHERE when it's a full table update.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.