Parent Child in varchar convert to integer

[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 SQL.
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'

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

 

[bborissov]

I suspect [bath] comes from sub_group but from where [bathsuba] comes?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Sammy145]

bath is a parent of bathsuba

NULL
-->BATH
---->bathsuba

 

[RyanEK]

Hi,

Your table logic doesn't seem quite right. What you should have is a Group field (parent) and a Parent_Group field. A sub_group field doesn't lead to hierarchical type data.

With your method, you need to duplicate parent records for every sub group.

Hmm or do you just have the field defs back to front? It would make sense if Parent_Group actually meant Sub_Group and vice-versa.

Ryan