Can I handle hierarchies like Oracle ?

Hi SQL Server Experts,
Oracle has a 'Connect by prior' extension to SQL that is neat for display hierarchies.
Does SQL Server have anything simialr or would I use T-SQL or ?
I will appreciate any suggestions and advice.
B.Dimple
Junior DBANo. I faced a similar problem in the past.|||As long as you have the base of the hierarcy defined you can add levels to the table information and create your hierarchy.

This is not an elegant solution but it does work.

Assume the following Table and Field names.

tblDepartment - table of organization departments
departmentNumber - department number of the selected organization
parentDepartmentNumber - department number of preceding branch
deptLevel - hierachical level from 1

(Only the base of the hierarchy will NOT have a parent associated.

step 1--
UPDATE tblDepartment
SET tblDepartment.deptLevel = 1
WHERE (tblDepartment.parentDepartmentNumber IS NULL)

step 2--
/* L2 Levels */
UPDATE tblDepartment
SET tblDepartment.deptLevel = 2
FROM tblDepartment
INNER JOIN tblDepartment as tblParent
ON tblParent.departmentNumber = tblDepartment.parentDepartmentNumber
WHERE tblParent.deptLevel = 1

continue step 2 for all levels

referencing--
SELECT DISTINCT
level1.departmentNumber AS departmentNumber1,
Level2.departmentNumber AS departmentNumber2,
Level3.departmentNumber AS departmentNumber3,
Level4.departmentNumber AS departmentNumber4,
Level5.departmentNumber AS departmentNumber5,
Level6.departmentNumber AS departmentNumber6,
Level7.departmentNumber AS departmentNumber7
FROM tblDepartment AS level7
RIGHT
JOIN
tblDepartment AS level6 ON
level7.PARENT_departmentNumber = Level6.departmentNumber
RIGHT JOIN
tblDepartment AS level5 ON
level6.PARENT_departmentNumber = Level5.departmentNumber
RIGHT JOIN
tblDepartment AS level4 ON
level5.PARENT_departmentNumber = Level4.departmentNumber
RIGHT JOIN
tblDepartment AS level3 ON
level4.PARENT_departmentNumber = Level3.departmentNumber
RIGHT JOIN
tblDepartment AS level2 ON
level3.PARENT_departmentNumber = Level2.departmentNumber
RIGHT JOIN
tblDepartment AS level1 ON
level2.PARENT_departmentNumber = Level1.departmentNumber|||Appologies BUT the field

"PARENT_departmentNumber" in the Joins should read

"parentDepartmentNumber"

I inadvertently copied my code incorrently|||What if you have 10 levels?

You actually need a loop to go to n levels...

but if you wait awhile...Yukon will have this...

And there are many ways to denormalize this...

Check this out:

http://www.sqlteam.com/item.asp?ItemID=8866|||Brett,

I agree. My first comment was that it was not an elegant solution.

I actually use the structure defined in the artice from your link to create a lineage (a DN in my case) for populating an X500 directory.

For me it was simple because the directory has a max of 6 levels off of the root DN.

I wish I had seen this article before I spent the time devloping the logic myself. ;)