008-SQL Server实战经验总结：如何将一对多的两个表中的某些列合并成一行显示？

需求场景

SQL Server有两个表mom_order、mom_proutingdetail，momorder中的MOMID和momproutingdetail中的MOMID是一对多的关系，momorder中的一个MOMID关联多行momproutingdetail，现在需要通过外键MOMID将mom_proutingdetail中匹配的行的procedureCode、procedureName字段分别合并成一列显示

最终想要的效果如下：

那么如何实现上述需求呢？

分析需求

要从mom_proutingdetail表中提取所有与mom_order表中特定MOMID相关的procedureCode和procedureName字段，并将它们合并为单个列表显示。

第一种实现方式，使用`STRING_AGG`关键字

可以使用SQL Server的STRING_AGG函数（需要使用的SQL Server版本支持此功能，即2017或更高版本中支持）

代码实现：

WITH CTE AS (
    SELECT 
        mo.MOMID,
        mp.procedureCode,
        mp.procedureName
    FROM mom_order mo
    INNER JOIN mom_proutingdetail mp ON mo.MOMID = mp.MOMID
)
SELECT 
    MOMID,
    STRING_AGG(procedureCode, ',') WITHIN GROUP (ORDER BY procedureCode) AS ProcedureCodes,
    STRING_AGG(procedureName, ',') WITHIN GROUP (ORDER BY procedureName) AS ProcedureNames
FROM CTE cte1
GROUP BY MOMID;

WITH CTE AS ( - 使用了一个公用表表达式（Common Table Expression，简称CTE），它允许我们在查询中创建一个临时的结果集，这个结果集可以被之后的查询引用。
SELECT mo.MOMID, mp.ProcedureCode, pp.ProcedureName - 这里选择了mom_order表中的MOMID字段，mom_proutingdetail表中的ProcedureCode字段，以及Proc_Procedure表中的ProcedureName字段。
FROM mom_order mo - 查询从mom_order表开始，mo是给这个表起的别名。
INNER JOIN mom_proutingdetail mp ON mo.MOMID = mp.MOMID - 使用INNER JOIN连接mom_order和mom_proutingdetail两个表，基于MOMID字段的匹配。mp是给mom_proutingdetail表起的别名。
INNER JOIN Proc_Procedure pp ON mp.ProcedureCode=pp.ProcedureCode - 再次使用INNER JOIN连接mom_proutingdetail和Proc_Procedure两个表，这次基于ProcedureCode字段的匹配。pp是给Proc_Procedure表起的别名。
) - 结束CTE的定义。
SELECT MOMID, - 主查询开始，选择MOMID字段。
STRING_AGG(procedureCode, ',') WITHIN GROUP (ORDER BY procedureCode) AS ProcedureCodes - 使用STRING_AGG函数将与每个MOMID相关联的所有ProcedureCode值聚合到一个由逗号分隔的字符串中。WITHIN GROUP (ORDER BY procedureCode)确保了字符串中的值是按ProcedureCode排序的。
,STRING_AGG(procedureName, ',') WITHIN GROUP (ORDER BY procedureName) AS ProcedureNames - 类似地，将所有ProcedureName值聚合到一个由逗号分隔的字符串中，同时按ProcedureName排序。
FROM CTE cte1 - 主查询从之前定义的CTE中获取数据，cte1是给CTE起的别名。
GROUP BY MOMID; - 按MOMID字段对结果进行分组，这意味着对于每个唯一的MOMID，都会生成一行结果，其中包含该MOMID下所有相关的ProcedureCode和ProcedureName的聚合字符串。

首先创建了一个CTE（公用表表达式），将两个表连接在一起。然后在主查询中，它使用STRING_AGG函数将每个MOMID下的procedureCode和procedureName值聚合到一个逗号分隔的字符串中。

如果允许报错：

消息 8711，级别 16，状态 1，第 47 行
同一作用域中的多个已排序聚合函数具有互相不兼容的排序。

在一个查询中使用了多个STRING_AGG()函数或其他排序聚合函数，并且这些函数的排序顺序不一致时会报错。

要解决这个问题，只需要确保所有STRING_AGG()函数使用相同的排序依据和排序方向。可以通过在所有WITHIN GROUP子句中使用相同的排序键来实现，例如：

SELECT 
    MOMID,
    STRING_AGG(procedureCode, ',') WITHIN GROUP (ORDER BY procedureCode) AS ProcedureCodes,
    STRING_AGG(procedureName, ',') WITHIN GROUP (ORDER BY procedureCode) AS ProcedureNames
FROM CTE cte1
GROUP BY MOMID;

在这个修正后的查询中，两个STRING_AGG()函数都按照procedureCode进行排序。这样，即使procedureName与procedureCode之间没有直接的排序关系，由于它们都是基于procedureCode进行排序的，因此不会产生冲突。

当然，也可以按照ProcedureName进行排序，将两个STRING_AGG()函数的排序键改为procedureName：

SELECT 
    MOMID,
    STRING_AGG(procedureCode, ',') WITHIN GROUP (ORDER BY procedureName) AS ProcedureCodes,
    STRING_AGG(procedureName, ',') WITHIN GROUP (ORDER BY procedureName) AS ProcedureNames
FROM CTE cte1
GROUP BY MOMID;

这样修改后，应该可以避免再出现上述错误。

第二种实现方式，使用STUFF关键字

如果当前的SQL Server版本不支持STRING_AGG，可以使用该方法。在SQL Server 2012及更早版本中，可以使用FOR XML PATH技巧来生成逗号分隔的字符串。

代码实现：

WITH CTE AS (
    SELECT 
        mo.MOMID,
        mp.procedureCode,
        mp.procedureName
    FROM mom_order mo
    INNER JOIN mom_proutingdetail mp ON mo.MOMID = mp.MOMID
)
SELECT 
    MOMID,
    STUFF((SELECT ',' + procedureCode 
           FROM CTE cte2 
           WHERE cte1.MOMID = cte2.MOMID
           ORDER BY procedureCode
           FOR XML PATH('')), 1, 1, '') AS ProcedureCodes,
    STUFF((SELECT ',' + procedureName 
           FROM CTE cte2 
           WHERE cte1.MOMID = cte2.MOMID
           ORDER BY procedureName
           FOR XML PATH('')), 1, 1, '') AS ProcedureNames
FROM CTE cte1
GROUP BY MOMID;