SQL多行合并为单列问题：用户分组报表中合并重复用户的UserGroupCode

Hey there! I get it—you’re trying to build a user report where each user only appears once, with all their associated UserGroupCode values combined into a single comma-separated field. The STUFF function can definitely help here, but it needs to be paired with grouping and XML path aggregation to work correctly. Let’s walk through the solution step by step.

The Problem with Your Current Query #

Your current JOIN query returns a row for every user-group relationship, which is why you’re seeing duplicate user entries with different UserGroupCode values. To fix this, we need to:

• Group the results by each unique user (using UserId is ideal since names might not be unique!)
• Aggregate all UserGroupCode values for each group into a single string.

Solution 1: Using STUFF + FOR XML PATH (Works for All SQL Server Versions) #

This is the classic method for string concatenation in older SQL Server versions. Here’s the adjusted query:

SELECT 
    u.LastName,
    u.FirstName,
    -- Combine UserGroupCode values into a comma-separated string
    STUFF(
        (
            SELECT ', ' + ug.UserGroupCode
            FROM dbo.UserGroupUser ugu
            JOIN dbo.UserGroup ug ON ug.UserGroupId = ugu.UserGroupId
            WHERE ugu.UserId = u.UserId  -- Match the outer query's user
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''  -- Remove the leading ", "
    ) AS CombinedUserGroups
FROM 
    dbo.[User] u
GROUP BY 
    u.UserId, u.LastName, u.FirstName  -- Group by unique user (UserId is critical!)
ORDER BY 
    u.LastName;

Key Notes:

• The correlated subquery fetches all UserGroupCode values specific to the user in the outer query.
• FOR XML PATH('') converts the subquery results into a tag-free XML string, and TYPE ensures special characters are handled correctly.
• .value('.', 'NVARCHAR(MAX)') extracts the text from the XML, while STUFF strips out the leading ", " that would otherwise appear.
• Always group by UserId—two users could share the same first/last name, so using the unique ID guarantees we’re grouping the right records.

Solution 2: Using STRING_AGG (SQL Server 2017+) #

If you’re on SQL Server 2017 or later, Microsoft’s built-in STRING_AGG function simplifies this drastically:

SELECT 
    u.LastName,
    u.FirstName,
    STRING_AGG(ug.UserGroupCode, ', ') AS CombinedUserGroups
FROM 
    dbo.[User] u
JOIN 
    dbo.UserGroupUser ugu ON u.UserId = ugu.UserId
JOIN 
    dbo.UserGroup ug ON ug.UserGroupId = ugu.UserGroupId
GROUP BY 
    u.UserId, u.LastName, u.FirstName
ORDER BY 
    u.LastName;

This function directly aggregates UserGroupCode values into a comma-separated string—no messy STUFF/XML workarounds required.

Why Your Previous STUFF Attempts Failed #

Chances are you weren’t pairing STUFF with a correlated subquery or proper grouping. The subquery ensures we only target user groups for the current row in the outer query, and grouping makes sure each user is represented exactly once in the final output.

Give either of these queries a go—they should deliver the single-row-per-user report you need, with all UserGroupCode values neatly combined!

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