User Tree

DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )

INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')

;WITH cteNodes AS
(
 --initialization
 SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
 FROM @tblUInfo
 --WHERE ParentUserID IS NULL

 UNION ALL

 ----recursive execution
 SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
 FROM cteNodes AS P
  INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)

, cteEmployeePath AS
(
 SELECT tblUInfo.UserID
  , tblUInfo.UserName
  ,(
   SELECT CAST(UserTree AS VARCHAR ) + ':'
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
   ORDER BY UserLevel DESC
   FOR XML PATH('')
   ) AS UserTree
  ,(
   SELECT MAX(UserLevel)
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
  ) AS UserLevel
 FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName 
FROM cteEmployeePath 
ORDER BY UserTree 
OPTION (MaxRecursion 32767)