SQL如何实现行转列动态展示多角色权限及多语言权限名称

问题背景 #

我有如下几张数据库表：

ProfilRights表结构及数据 #

ProfilRightId    ProfilId    AccessId     Right 
-------------    --------    --------    --------    
      1             1           1           full  
      2             1           2           r-o
      3             2           1           none
      4             2           2           full

Profil表结构及数据 #

ProfilId    ProfilName    
--------    ----------    
   1           IT            
   2           Admin        

Access表结构及数据 #

AcessId       AcessName    
--------      ----------    
   1          Employess
   2          Clients

Access多语言翻译表AccessTranslation结构及数据 #

AccessTransId   AcessId       TranslatedLabel   Language
-------------   --------      ---------------   -------- 
      1           1           Employees         English
      2           2           Clients           English
      3           1           EmployeesFR       French
      4           2           ClientsFR         French

需求说明 #

期望查询结果按如下格式展示：

EnglishAcess    FrenchAcess     IT        Admin
-------------    --------      ------     ------
Employees        EmployeesFR   full        none    
Clients          ClientsFR      r-o        full

同时要求支持Profil动态扩展：若新增Director等角色并配置权限后，查询结果可自动在右侧新增对应角色的权限列。

现有问题 #

当前编写的查询语句如下，无法得到预期结果：

SELECT P.ProfilName, AT.TranslatedLabel,  PR.Right
FROM ProfilRights PR
INNER JOIN Profil P ON PR.PofilId = P.PofilId
INNER JOIN Access A ON PR.AccessId = A.AccessId
INNER JOIN AccessTranslation [AT] ON A.AccessId = AT.AccessId;

当前查询返回的是行式数据，如下所示：

ProfilName    TranslatedLabel     Right
-----------   --------------      ------
Admin         EmployeesFR         none
Admin         ClientsFR           full        
Admin         Employees           none
Admin         Clients             full                
IT            EmployeesFR         full        
IT            ClientsFR           r-o        
IT            Employees           full        
IT            Clients             r-o 

之前的语句返回多行重复结果，是因为每个访问项对应2种语言翻译，每个翻译又对应多个角色权限，产生了笛卡尔积。要实现需求需要用动态行转列（动态透视）逻辑，不同数据库的实现语法略有差异，以下以MySQL为例给出实现方案：

实现代码 #

第一步：先聚合多语言字段，生成基础访问项行 #

WITH base_access AS (
    SELECT 
        A.AcessId,
        MAX(CASE WHEN AT.Language = 'English' THEN AT.TranslatedLabel END) AS EnglishAcess,
        MAX(CASE WHEN AT.Language = 'French' THEN AT.TranslatedLabel END) AS FrenchAcess
    FROM Access A
    INNER JOIN AccessTranslation AT ON A.AcessId = AT.AcessId
    GROUP BY A.AcessId
)

第二步：动态拼接透视列的SQL语句 #

SET @sql = NULL;
-- 自动读取所有Profil生成透视列逻辑
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN P.ProfilName = ''',
        ProfilName,
        ''' THEN PR.Right END) AS `',
        ProfilName, '`'
    )
) INTO @sql
FROM Profil;

-- 拼接完整查询语句
SET @sql = CONCAT('
    SELECT ba.EnglishAcess, ba.FrenchAcess, ', @sql, '
    FROM base_access ba
    LEFT JOIN ProfilRights PR ON ba.AcessId = PR.AccessId
    LEFT JOIN Profil P ON PR.ProfilId = P.ProfilId
    GROUP BY ba.AcessId, ba.EnglishAcess, ba.FrenchAcess
    ORDER BY ba.AcessId
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

其他数据库适配说明 #

• PostgreSQL：用crosstab函数结合动态SQL实现
• SQL Server：用PIVOT关键字结合动态SQL实现
• Oracle：用PIVOT关键字结合EXECUTE IMMEDIATE实现动态拼接

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