遞歸 CTE 多級

我有一個表格，顯示哪些角色遞歸地授予對數據庫中哪些資源的訪問權限。例如：

Default_Role 授予對 App_Role 的訪問權限，App_Role 授予對 Security_Role 的訪問權限，Security_Role 授予對三個表（customers、sales、users）的訪問權限。因此，Default_Role 的成員被授予所有這些權限，但 Default_Role 沒有被授予對 Sys_Role 的訪問權限，也沒有被授予對 System 或 Admin 表的訪問權限。

CREATE TABLE SQLTest(
    DBName     NVARCHAR(100) NULL
   ,Privilege    NVARCHAR(100) NULL
   ,PrivilegeType NVARCHAR(100) NULL
   ,PrivilegeDetail NVARCHAR(100) NULL
   ,TableName NVARCHAR(100) NULL
)

INSERT INTO SQLTest
VALUES 
('TSDB','Default_Role','Role','App_Role',NULL),
('TSDB','App_Role','Role','Security_Role',NULL),
('TSDB','Sys_Role','Role','Security_Role',NULL),
('TSDB','Security_Role','Table','Customers','Customers'),
('TSDB','Security_Role','Table','Sales','Sales'),
('TSDB','Security_Role','Table','Users','Users'),
('TSDB','Sys_Role','Table','System','System'),
('TSDB','Sys_Role','Table','Admin','Admin')

將其展平的最佳方法是什麼，以便您可以查看授予 Default_Role 的所有訪問權限（角色和表），而不會顯示未授予 Default_Role 的其他訪問權限？像這樣：

試著把這個例子放在一起，但不起作用。

http://www.sqlfiddle.com/#!18/f9a27/2

由於 CTE 的第一個測試很難理解，我寫了一個方法，所有級別都在最後

最後的選項是不需要的，但是當你試圖弄清楚會發生什麼時，特別是要減少，你應該以較低的數字添加它

WITH MyTest as
(
 SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P.PrivilegeDetail AS VarChar(Max)) as Level
 FROM SQLTest P
 WHERE P.Privilege = 'Default_Role'

 UNION ALL

 SELECT P1.DBName, P1.Privilege,p1.PrivilegeType,P1.PrivilegeDetail,p1.TableName , CAST(P1.PrivilegeDetail AS VarChar(Max)) + ', ' + M.Level
 FROM SQLTest P1  
 INNER JOIN MyTest M
 ON M.PrivilegeDetail = P1.Privilege
)
SELECT * From MyTest

OPTION (MAXRECURSION 50);

結果

DBName  Privilege       PrivilegeType   PrivilegeDetail     TableName   Level
TSDB    Default_Role    Role            App_Role            (null)       App_Role
TSDB    App_Role        Role            Security_Role       (null)       Security_Role, App_Role
TSDB    Security_Role   Table           Customers           Customers    Customers, Security_Role, App_Role
TSDB    Security_Role   Table           Sales               Sales        Sales, Security_Role, App_Role
TSDB    Security_Role   Table           Users                Users       Users, Security_Role, App_Role

引用自：https://dba.stackexchange.com/questions/279665