SQL 樹 遞迴 查詢所有子節點

WITH T AS
(
SELECT CID,ParentID
    , 0 AS Level
    FROM SYS_Category
    WHERE CID = 8
     UNION ALL
    SELECT c.CID,c.ParentID, Level +1
    FROM SYS_Category c
    inner JOIN T t1
    ON c.ParentID=t1.CID
)
SELECT * FROM T;

SQL 子查詢 文字累加

select u.*,
(select CONVERT(nvarchar(max),f.RID ) + ','
from UserRoleRelation f
 where u.UID = f.UID for xml path('')) role
from SYS_User u