SQL关联查询实现行转列，max(case)与pivot()方法试用无效求助

首先提供兼容所有主流数据库的CASE WHEN聚合实现方案，无需依赖数据库特有的pivot函数：

WITH base_data AS (
    -- 保留你原有的关联查询逻辑作为基础数据集
    SELECT GROUP_ID, ROLE_ID, USER_ID, First_Name, Last_name, Phone
    FROM GroupRoles
    FULL JOIN Account
    ON Account.Account_ID = GroupRoles.User_ID
)
SELECT 
    GROUP_ID,
    -- 以下为ROLE_ID=1对应的用户字段，ROLE_ID取值请替换为你实际的角色枚举值
    MAX(CASE WHEN ROLE_ID = 1 THEN USER_ID END) AS role1_user_id,
    MAX(CASE WHEN ROLE_ID = 1 THEN First_Name END) AS role1_first_name,
    MAX(CASE WHEN ROLE_ID = 1 THEN Last_name END) AS role1_last_name,
    MAX(CASE WHEN ROLE_ID = 1 THEN Phone END) AS role1_phone,
    -- ROLE_ID=2对应的用户字段
    MAX(CASE WHEN ROLE_ID = 2 THEN USER_ID END) AS role2_user_id,
    MAX(CASE WHEN ROLE_ID = 2 THEN First_Name END) AS role2_first_name,
    MAX(CASE WHEN ROLE_ID = 2 THEN Last_name END) AS role2_last_name,
    MAX(CASE WHEN ROLE_ID = 2 THEN Phone END) AS role2_phone,
    -- 有更多角色按上述格式新增即可
    MAX(CASE WHEN ROLE_ID = 3 THEN USER_ID END) AS role3_user_id,
    MAX(CASE WHEN ROLE_ID = 3 THEN First_Name END) AS role3_first_name,
    MAX(CASE WHEN ROLE_ID = 3 THEN Last_name END) AS role3_last_name,
    MAX(CASE WHEN ROLE_ID = 3 THEN Phone END) AS role3_phone
FROM base_data
GROUP BY GROUP_ID
ORDER BY GROUP_ID;

如果你使用的是支持pivot函数的数据库（如SQL Server、Oracle），可以使用多列透视写法：

WITH base_data AS (
    SELECT GROUP_ID, ROLE_ID, USER_ID, First_Name, Last_name, Phone
    FROM GroupRoles
    FULL JOIN Account
    ON Account.Account_ID = GroupRoles.User_ID
)
SELECT *
FROM base_data
PIVOT (
    MAX(USER_ID) AS user_id,
    MAX(First_Name) AS first_name,
    MAX(Last_name) AS last_name,
    MAX(Phone) AS phone
    FOR ROLE_ID IN ([1], [2], [3]) -- 替换为实际的ROLE_ID枚举值即可
) AS pvt
ORDER BY GROUP_ID;

注意事项 #

• 上述静态写法仅适用于角色枚举值固定的场景，如果ROLE_ID是动态不确定的，需要编写动态SQL拼接生成查询语句
• 若使用MySQL 8.0以下版本不支持CTE语法，直接将base_data内的查询语句放到FROM后作为子查询即可
• 原查询的FULL JOIN可根据你的实际数据关联需求替换为INNER JOIN或LEFT JOIN优化查询性能

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