There are a couple ways of doing this. The simplest method is to use a recursive CTE.

Something like the following...

-- Create some test date...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp (

ChildID INT,

ParentID INT,

ChildName VARCHAR(50)

);

INSERT #temp (ChildID, ParentID, ChildName) VALUES

(1, NULL, 'Aaron'), (2, 1, 'Bob'), (3, 1, 'Clay'), (4, 1, 'Darrin'), (5, 2, 'Edward'), (6, 2, 'Frank'), (7, 2, 'Gail'),

(8, 3, 'Hugo'), (9, 3, 'Izzy'), (10, 3, 'Jason'), (11, 3, 'Karl'), (12, 4, 'Lary'), (13, 4, 'Mark'), (14, 4, 'Nancy'),

(15, 4, 'Osmund'), (16, 4, 'Perry'), (17, 5, 'Quincy'), (18, 5, 'Ray'), (19, 5, 'Sam'), (20, 5, 'Toni'), (21, 6, 'Ulrick'),

(22, 6, 'Vinny'), (23, 6, 'Wesley'), (24, 6, 'Xavier'), (25, 6, 'Yancy'), (26, 7, 'Zoe'), (27, 7, 'Axel'), (28, 7, 'Ben'),

(29, 8, 'Craig'), (30, 8, 'Don'), (31, 9, 'Eddy'), (32, 9, 'Fry'), (33, 9, 'Greg'), (34, 10, 'Heidi'), (35, 10, 'Ilean'),

(36, 10, 'Joe'), (37, 10, 'Kevin'), (38, 10, 'Lou');

-- Recursive CTE method

WITH Hierarchy AS (

SELECT-- Define the anchor node.

t.ChildID,

t.ParentID,

t.ChildName,

0 AS NodeLevel

FROM

#temp t

WHERE

t.ParentID IS NULL

UNION ALL

SELECT-- Add the recursive nodes.

t.ChildID,

t.ParentID,

t.ChildName,

h.NodeLevel + 1 AS NodeLevel

FROM

#temp t

JOIN Hierarchy h

ON t.ParentID = h.ChildID

)

SELECT

h.ChildID,

h.ParentID,

h.ChildName,

h.NodeLevel

FROM

Hierarchy h

ORDER BY

h.NodeLevel,

h.ParentID,

h.ChildID

--OPTION (MAXRECURSION 200)-- Only needed if there is a possibilty that you go more than 100 levels deep

;

If you need better performance, do some research on the "Nested Sets Model". Joe Celko has written extensively on the topic and Jeff Moden has an awesome article here on SSC[/url] that includes, what is to the best of my knowledge, the fastest method for populating the Left & Right bowers that are needed for the model.

HTH,

Jason