仅用SELECT语句实现SQL动态PIVOT的可行方案咨询

由于处于只读SQL环境（仅允许SELECT），无法使用动态SQL变量声明、临时表创建等操作，常规动态PIVOT方案受限，以下是两种适配场景的可行方法：

方法1：生成可执行的静态透视SQL语句（手动执行） #

通过字符串聚合函数提取所有唯一OPT值，自动拼接成SUM(IIF/CASE)格式的静态透视语句，复制生成的语句执行即可得到权限矩阵，适配OPT的动态变化。

SQL Server示例 #

SELECT 
    'SELECT CAT_ID, USER_ID,' + 
    STRING_AGG(
        CONCAT('SUM(IIF(OPT = ''', OPT, ''', 1, 0)) AS [', OPT, ']'),
        ','
    ) + 
    ' FROM MyTable GROUP BY CAT_ID, USER_ID' AS PivotSQL
FROM (SELECT DISTINCT OPT FROM MyTable) AS DistinctOpts

MySQL 8.0+示例 #

SELECT 
    CONCAT(
        'SELECT CAT_ID, USER_ID,',
        GROUP_CONCAT(
            CONCAT('SUM(IF(OPT = ''', OPT, ''', 1, 0)) AS `', OPT, '`')
            SEPARATOR ','
        ),
        ' FROM MyTable GROUP BY CAT_ID, USER_ID'
    ) AS PivotSQL
FROM (SELECT DISTINCT OPT FROM MyTable) AS DistinctOpts

PostgreSQL示例 #

SELECT 
    'SELECT CAT_ID, USER_ID,' ||
    STRING_AGG(
        'SUM(CASE WHEN OPT = ''' || OPT || ''' THEN 1 ELSE 0 END) AS "' || OPT || '"',
        ','
    ) ||
    ' FROM MyTable GROUP BY CAT_ID, USER_ID' AS PivotSQL
FROM (SELECT DISTINCT OPT FROM MyTable) AS DistinctOpts;

方法2：返回JSON/XML格式的动态权限矩阵（直接查询返回） #

如果不需要严格的列转行表格，可通过JSON/XML聚合函数将用户权限打包成结构化字段，直接适配OPT动态变化。

SQL Server示例 #

SELECT 
    CAT_ID,
    USER_ID,
    (SELECT OPT FROM MyTable t2 WHERE t2.CAT_ID = t1.CAT_ID AND t2.USER_ID = t1.USER_ID FOR JSON AUTO) AS UserPermissions
FROM MyTable t1
GROUP BY CAT_ID, USER_ID

MySQL 8.0+示例 #

SELECT 
    CAT_ID,
    USER_ID,
    JSON_ARRAYAGG(DISTINCT OPT) AS UserPermissions
FROM MyTable
GROUP BY CAT_ID, USER_ID

PostgreSQL示例 #

SELECT 
    CAT_ID,
    USER_ID,
    JSON_AGG(DISTINCT OPT) AS UserPermissions
FROM MyTable
GROUP BY CAT_ID, USER_ID

临时表方案失败的原因 #

只读环境通常禁止创建临时表（需CREATE TABLE权限），依赖临时表存储OPT列名再关联PIVOT的方案必然无法执行，上述两种方法均仅基于SELECT操作，完全规避写权限要求。

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