Wednesday, June 9, 2010

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)

No comments: