SQL Server 2016：将多行角色名合并为单行逗号分隔列

将同一员工对应的多个角色名合并为单条记录，角色名以逗号分隔，不同数据库的实现方式如下：

MySQL/MariaDB #

使用GROUP_CONCAT()聚合函数，配合GROUP BY按员工UUID分组：

SELECT    
    EMP.Employee_UUID AS ID,
    GROUP_CONCAT(ER.Role_Name SEPARATOR ', ') AS Roles
FROM
    Employees_To_Roles AS E2R 
INNER JOIN
    Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                         AND E2R.App_id = ER.App_id 
INNER JOIN
    Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     
    E2R.App_id = 2
GROUP BY EMP.Employee_UUID;

SQL Server 2017+/Azure SQL #

使用STRING_AGG()函数：

SELECT    
    EMP.Employee_UUID AS ID,
    STRING_AGG(ER.Role_Name, ', ') AS Roles
FROM
    Employees_To_Roles AS E2R 
INNER JOIN
    Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                         AND E2R.App_id = ER.App_id 
INNER JOIN
    Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     
    E2R.App_id = 2
GROUP BY EMP.Employee_UUID;

Oracle 11gR2+ #

使用LISTAGG()函数，可指定拼接顺序：

SELECT    
    EMP.Employee_UUID AS ID,
    LISTAGG(ER.Role_Name, ', ') WITHIN GROUP (ORDER BY ER.Role_Name) AS Roles
FROM
    Employees_To_Roles AS E2R 
INNER JOIN
    Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                         AND E2R.App_id = ER.App_id 
INNER JOIN
    Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     
    E2R.App_id = 2
GROUP BY EMP.Employee_UUID;

PostgreSQL 9.0+ #

使用STRING_AGG()函数，支持排序：

SELECT    
    EMP.Employee_UUID AS ID,
    STRING_AGG(ER.Role_Name, ', ') ORDER BY ER.Role_Name AS Roles
FROM
    Employees_To_Roles AS E2R 
INNER JOIN
    Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                         AND E2R.App_id = ER.App_id 
INNER JOIN
    Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     
    E2R.App_id = 2
GROUP BY EMP.Employee_UUID;

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