SQL Server 2018查询：将最后一列多行值合并为逗号分隔单字段

嘿，针对你遇到的重复行问题，SQL Server 2018自带的STRING_AGG()函数正好能完美解决这个需求——它可以把分组内的多行值合并成单个逗号分隔的字符串。下面是修改后的查询语句：

SELECT 
    CC.FirstName, 
    CC.LastName, 
    CC.Email, 
    Addresses.City, 
    Addresses.State, 
    Addresses.Postal,
    -- 合并SP.Name为逗号分隔的字符串，可选排序保证结果一致
    STRING_AGG(SP.Name, ', ') WITHIN GROUP (ORDER BY SP.Name) AS SalesProgramNames
FROM CustContacts AS CC WITH (NoLock)
INNER JOIN Orders AS O WITH (Nolock) ON CC.CustContactID = O.ContactID
INNER JOIN Customers AS C WITH (NoLock) ON O.CustomerID = C.CustomerID
INNER JOIN SalesPrograms AS SP WITH (NoLock) ON O.SalesProgramID = SP.SalesProgramID
INNER JOIN OrderLines AS OL WITH (NoLock) ON O.OrderID = OL.OrderID
INNER JOIN RMEvents AS RME WITH (NoLock) ON OL.EventID = RME.EventID
INNER JOIN Addresses ON CC.AddressID = Addresses.AddressID
LEFT OUTER JOIN OEGroupVisits AS GV WITH (NoLock) ON O.GroupVisitID = GV.GroupVisitID
-- 按所有非聚合字段分组，确保同一用户只返回一行
GROUP BY 
    CC.FirstName, 
    CC.LastName, 
    CC.Email, 
    Addresses.City, 
    Addresses.State, 
    Addresses.Postal

关键说明： #

• 去掉了原查询的DISTINCT，改用GROUP BY来分组聚合，这是合并多值的核心前提
• STRING_AGG(SP.Name, ', ')指定要合并的列和分隔符，WITHIN GROUP (ORDER BY SP.Name)可选，用来保证合并后的字符串顺序固定（比如按名称排序）
• 分组字段必须包含所有非聚合的查询列，否则会触发SQL语法错误

额外需求：限制最多合并5个值 #

如果需要确保每个用户的SalesProgramNames最多只显示5个值，可以先通过窗口函数筛选前5个，再进行聚合：

WITH RankedSalesPrograms AS (
    SELECT 
        CC.FirstName, 
        CC.LastName, 
        CC.Email, 
        Addresses.City, 
        Addresses.State, 
        Addresses.Postal,
        SP.Name,
        -- 按用户分组，给每个销售程序排序取前5
        ROW_NUMBER() OVER (PARTITION BY CC.CustContactID ORDER BY SP.Name) AS ProgramRank
    FROM CustContacts AS CC WITH (NoLock)
    INNER JOIN Orders AS O WITH (Nolock) ON CC.CustContactID = O.ContactID
    INNER JOIN Customers AS C WITH (NoLock) ON O.CustomerID = C.CustomerID
    INNER JOIN SalesPrograms AS SP WITH (NoLock) ON O.SalesProgramID = SP.SalesProgramID
    INNER JOIN OrderLines AS OL WITH (NoLock) ON O.OrderID = OL.OrderID
    INNER JOIN RMEvents AS RME WITH (NoLock) ON OL.EventID = RME.EventID
    INNER JOIN Addresses ON CC.AddressID = Addresses.AddressID
    LEFT OUTER JOIN OEGroupVisits AS GV WITH (NoLock) ON O.GroupVisitID = GV.GroupVisitID
)
SELECT 
    FirstName, 
    LastName, 
    Email, 
    City, 
    State, 
    Postal,
    STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS SalesProgramNames
FROM RankedSalesPrograms
WHERE ProgramRank <= 5
GROUP BY 
    FirstName, 
    LastName, 
    Email, 
    City, 
    State, 
    Postal

这样就能保证每个用户的合并字段最多包含5个销售程序名称啦。

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