convert a table into tree

Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.

suppose table is like this. username parentid downline

B A left

C A right

D B left

E B right...

I have to also determine the level in the tree...please help...

CREATETABLE [dbo].[#tree2](

[username] [char](1),

[parentid] [char](1),

[downline] [nvarchar](50),

[id] [int]NOTNULL

)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('b','a','left',1)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('c','a','right',2)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('d','b','left',3)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('e','b','right',4)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('a',NULL,'right',5)

;WITH myCTE(levels, parentid, username, tree, downline)

AS

(SELECT 1, parentid, username,CAST(usernameasvarchar(50)),downline

FROM #tree2

WHERE parentidisNULL

UNIONALL

SELECT t1.levels+1,

t2.parentid,

t2.username,

CAST(tree+'/'+ t2.usernameASvarchar(50)),

t2.downline

FROM myCTE t1JOIN #tree2 t2ON t1.username=t2.parentid

WHERE t1.levels<10--you can change this up to the level you want

)

SELECT levels, parentid, username, downline, treeFROM myCTE

ORDERBY levels

DROPTABLE #tree2