Combining values into a single column if more than 1 matches the criteria

Question

I am trying to write a query to return the users, some info about them and which security group they're assigned to. Some users have more than one group however, so I instead of returning 2 rows, I would like to combine the security group names.

SELECT  ur.loginname 
       ,ur.firstname 
       ,ur.lastname 
       ,sg.securitygroupname 
       ,jt.description 
FROM    users ur 
       ,usersecuritygroup us 
       ,securitygroup sg
       ,jobtitle jt 
WHERE  ur.doctorfacilityid = us.doctorfacilityid 
       AND us.securitygroupid = sg.securitygroupid 
       AND ur.jobtitle = jt.jtid 

So instead of getting 2 rows like this:

jdoe     john     doe     group1     xyz
jdoe     john     doe     group2     xyz

I would like to return 1 row like this:

jdoe     john     doe     group1, group2   xyz

The security group would be the only column that would have more than one row to meet the criteria (if that matters).

Answer 1

You can put your query into a CTE and use XML to concatenate the values in securitygroupname column or if you are using SQL Server 2017 you can use STRING_AGG() function.

with cte as (
    SELECT  ur.loginname 
           ,ur.firstname 
           ,ur.lastname 
           ,sg.securitygroupname 
           ,jt.description 
    FROM    users ur 
           ,usersecuritygroup us 
           ,securitygroup sg
           ,jobtitle jt 
    WHERE  ur.doctorfacilityid = us.doctorfacilityid 
           AND us.securitygroupid = sg.securitygroupid 
           AND ur.jobtitle = jt.jtid
)
-- OLD VERSIONS
select distinct
    loginname
    ,firstname
    ,lastname
    ,STUFF(
        (SELECT
            N', ' + securitygroupname
        FROM cte c
        WHERE loginname = c.loginname
            and firstname = c.firstname
            and lastname = c.lastname
            and description = c.description
        FOR XML PATH(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, N''
    ) securitygroupname
    ,description
from cte

-- STARTING SQL SERVER 2017
select
    loginname
    ,firstname
    ,lastname
    ,STRING_AGG(securitygroupname, ',') as securitygroupname
    ,description
from cte
group by loginname, firstname, lastname, description