Recursive Hierarchy - 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 Hierarchy

Author

Topic

JNOM
Starting Member

3 Posts

Posted - 2002-05-26 : 17:57:49

I noticed that a lot of people read my previous topic but no answers so this is what I have,

TBL_Hierarchy
id Group Category Family Line Model
-------------------------------------------------------------
1 Imaging DOP DOP - HDM ManRodland Komori
2 Imaging DOP DOP - HDM ManRodland Maxwell
3 Imaging DOP DOP - HDM Legacy Thermal I
4 Imaging DOP DOP - HDM Legacy Thermal II
etc...

This is what I want to accomplish

ID HierarchyName ParentID
--------------------------------------
1 Imaging 0
2 DOP 1
3 DOP - HDM 2
4 ManRoland 3
5 Komori 4
6 Maxwell 4
7 Legacy 3
8 Thermal I 7
9 Thermal II 7
etc..

So what I'm trying to build is a query or sp that would populate the second table, any input will be greatly welcomed!!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-26 : 20:43:13

create table a
(
id int identity ,
name varchar(100) ,
pid int
)

insert a
select distinct Group, 0 from tbl

insert a
select distinct Category, (select id from a where name = Group and pid = 0)
from tbl

......

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources