Hello Sean,

Here is the DDL statment and sample data

DECLARE @sys_user TABLE

(UserId bigint

,FRST_NM varchar(100)

,LST_NM varchar(100)

,APROV_ID bigint)

Insert into @sys_user

Select 800000000, 'Dave', 'Smith', NULL

UNION Select 810000000, 'Michael', 'Chak',800000000

UNION Select 811000000, 'David', 'Boon',810000000

UNION Select 811100000, 'Glen','Macgrath',811000000

UNION Select 811110000, 'Alex', 'Boon',811100000

UNION Select 811111000, 'Jim', 'Foley',811111000

UNION Select 821000000,'Stephan', '',800000000

--select * from @sys_user

DECLARE @userid BIGINT--=800000000

Set @userid=800000000

;With submenu (UserId,Name,APROV_ID,lvl)

AS

(

Select UserId, FRST_NM + ' '+LST_NM as Name,APROV_ID, 0 lvl

From @sys_user

Where UserId=@UserId

Union ALL Select

su.UserId

,su.FRST_NM + ' '+su.LST_NM

,su.APROV_ID

,sm.lvl+1

From @sys_user su

Join submenu sm ON su.APROV_ID=sm.UserId

)

Select SPACE(lvl*2)+STR(UserID) + '|| '+Name,lvl

From submenu

Order by UserId

Hope it will work for you.Please let me know if you need any more information.

Thanks

Rohit