quote:

---

Originally posted by visakh16

Refer this for an illustration

http://support.microsoft.com/kb/248915

---

set nocount on

CREATE TABLE hierarchy
        (parent VARCHAR(20) NOT NULL,
         child VARCHAR(20),
CONSTRAINT UIX_parentchild
         UNIQUE NONCLUSTERED (parent,child)
)
CREATE CLUSTERED INDEX CIX_parent
 ON hierarchy(parent)
GO

INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')
GO

declare @t table (parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parent, child, lev, fullpath)
select distinct null, p.parent, @lev, p.parent
from   hierarchy p
left join   hierarchy c on c.child = p.parent
where  c.child is null

while @@rowcount > 0
begin
       set @lev = @lev + 1

       --Get all children of current level
       insert @t (parent, child, lev, fullpath)
       select h.parent, h.child, @lev, t.fullpath + '.' + h.child
       from   @t t
       join   hierarchy h on h.parent = t.child and t.lev = @lev-1

       --make sure a circular reference doesn't put is in an infinate loop
       left join @t x on x.parent = h.parent and x.child = h.child
       where x.parent is null
end

print 'helper table'
select * from @t order by fullpath

print 'one way to display the hierarchy'
select replicate(char(9), lev) + child from @t order by fullpath

go
drop table hierarchy

--=============================================
output:
helper table
parent               child                lev         fullpath
-------------------- -------------------- ----------- ------------------------------------------------------
NULL                 World                0           World
World                Europe               1           World.Europe
Europe               France               2           World.Europe.France
France               Paris                3           World.Europe.France.Paris
World                North America        1           World.North America
North America        Canada               2           World.North America.Canada
North America        United States        2           World.North America.United States
United States        New York             3           World.North America.United States.New York
New York             New York City        4           World.North America.United States.New York.New York City
United States        Washington           3           World.North America.United States.Washington
Washington           Redmond              4           World.North America.United States.Washington.Redmond

one way to display the hierarchy
--------------------------------------------------------------------------------------------------------------
World
	Europe
		France
			Paris
	North America
		Canada
		United States
			New York
				New York City
			Washington
				Redmond