如何动态生成应用权限矩阵？CTE+Pivot是否可行？

这需求我太熟了，要做能自动适配角色、菜单、权限变化的动态权限矩阵，用CTE处理菜单层级+动态Pivot转换行列完全可行，刚好适配你说的最多4级菜单的场景。我给你一步步拆解实现：

核心思路 #

1. 统一菜单层级展示：用CTE把多级菜单拼接成完整路径（比如Source Data > Cupboards），让矩阵里的菜单列清晰体现层级关系；
2. 动态适配可变项：通过动态SQL自动获取所有角色和权限标识，避免硬编码，实现新增角色/权限时矩阵自动更新；
3. Pivot转换行列：把行格式的权限数据转成矩阵形式，菜单作为行，角色+权限组合为列，直观展示每个角色对各菜单的权限情况。

具体实现代码 #

-- 第一步：用CTE生成带完整路径的菜单数据（适配最多4级菜单）
WITH MenuHierarchy AS (
    -- 根菜单（ParentMenuName为NULL）
    SELECT 
        RoleID,
        RoleName,
        IsReadOnly,
        MenuName AS FullMenuPath,
        MenuName,
        ParentMenuName,
        Regulierer,
        Station
    FROM #question
    WHERE ParentMenuName IS NULL

    UNION ALL

    -- 二级菜单（根菜单的子级）
    SELECT 
        q.RoleID,
        q.RoleName,
        q.IsReadOnly,
        CONCAT(mh.FullMenuPath, ' > ', q.MenuName) AS FullMenuPath,
        q.MenuName,
        q.ParentMenuName,
        q.Regulierer,
        q.Station
    FROM #question q
    JOIN MenuHierarchy mh ON q.ParentMenuName = mh.MenuName
    WHERE mh.ParentMenuName IS NULL

    UNION ALL

    -- 三级菜单（二级菜单的子级）
    SELECT 
        q.RoleID,
        q.RoleName,
        q.IsReadOnly,
        CONCAT(mh.FullMenuPath, ' > ', q.MenuName) AS FullMenuPath,
        q.MenuName,
        q.ParentMenuName,
        q.Regulierer,
        q.Station
    FROM #question q
    JOIN MenuHierarchy mh ON q.ParentMenuName = mh.MenuName
    WHERE CHARINDEX(' > ', mh.FullMenuPath) > 0

    UNION ALL

    -- 四级菜单（三级菜单的子级）
    SELECT 
        q.RoleID,
        q.RoleName,
        q.IsReadOnly,
        CONCAT(mh.FullMenuPath, ' > ', q.MenuName) AS FullMenuPath,
        q.MenuName,
        q.ParentMenuName,
        q.Regulierer,
        q.Station
    FROM #question q
    JOIN MenuHierarchy mh ON q.ParentMenuName = mh.MenuName
    WHERE CHARINDEX(' > ', mh.FullMenuPath, CHARINDEX(' > ', mh.FullMenuPath)+1) > 0
),
-- 第二步：整理权限数据，将权限标识与角色组合成唯一列名
PermissionData AS (
    SELECT
        FullMenuPath,
        'IsReadOnly_' + RoleName AS PermissionColumn,
        CAST(IsReadOnly AS VARCHAR(10)) AS PermissionValue
    FROM MenuHierarchy
    UNION ALL
    SELECT
        FullMenuPath,
        'Regulierer_' + RoleName AS PermissionColumn,
        CAST(Regulierer AS VARCHAR(10)) AS PermissionValue
    FROM MenuHierarchy
    UNION ALL
    SELECT
        FullMenuPath,
        'Station_' + RoleName AS PermissionColumn,
        CAST(Station AS VARCHAR(10)) AS PermissionValue
    FROM MenuHierarchy
)
-- 第三步：动态生成Pivot列并执行SQL
DECLARE @PivotColumns NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 自动获取所有需要Pivot的权限列（适配新增角色/权限）
SELECT @PivotColumns = STRING_AGG(QUOTENAME(PermissionColumn), ', ')
FROM (SELECT DISTINCT PermissionColumn FROM PermissionData) t

-- 构建动态Pivot语句
SET @SQL = N'
SELECT FullMenuPath AS [Menu Path], ' + @PivotColumns + N'
FROM PermissionData
PIVOT (
    MAX(PermissionValue)
    FOR PermissionColumn IN (' + @PivotColumns + N')
) p
ORDER BY FullMenuPath'

-- 执行动态SQL
EXEC sp_executesql @SQL

方案优势 #

• 完全动态适配：新增角色、菜单、权限标识（比如再加个Delete权限列）时，无需修改代码，重新运行就能自动更新矩阵；
• 菜单层级清晰：通过CTE拼接的完整路径，直观展示从根到叶子的菜单结构，完美适配最多4级的需求；
• 性能可控：CTE的递归逻辑限制了最多4级，不会出现无限递归问题，面对大量角色和菜单的场景也能稳定运行。

结果示例 #

基于你提供的测试数据，运行代码后会得到如下形式的权限矩阵：

内容的提问来源于stack exchange，提问作者kpollock