Merge multiple rows into a column while replacing the keys with a 'name' field from the or-CodePudding

I've got a join table that has 'account_id' and 'group id' in it, both represented by a GUID. I'm trying to merge the columns so we get a single 'account_id' with all of it's 'group_id's merged to a single Colum/row, but display the actual name of the account and group.

Tables Account

account_id   Name
1            Henry
2            Smith

Group

Group_id   Group_nameName
3            User
4            Admin

Account_Group_Join_Table

account_id   group_id
1            3
1            4
2            3

Desired Result:

Name    Groups
Henry   Dev,User
Smith   User, Admin

Code so far to return 'account_id' with 'group_id' grouped to single row/column

select account_id,
  stuff((SELECT distinct ', '   group_id
           FROM account_group_join t2
           where t2.account_id = t1.account_id
           FOR XML PATH('')),1,1,'') [Groups]
from account_group_join t1
group by account_id

CodePudding user response：

You just need to join to your group table in the subquery so you can get the name rather than the ID. You also may as well just select from Account in the outer query and avoid an additional join to account to get the name:

SELECT  a.account_id,
        a.Name,
      STUFF((SELECT DISTINCT ', '   g.group_nameName
               FROM account_group_join AS ag
                    INNER JOIN [Group] AS g
                        ON g.group_id = ag.group_id
               WHERE ag.account_id = a.account_id
               FOR XML PATH('')),1,1,'') AS [Groups]
from Account AS a;

n.b. I've changed your aliases from t1 and t2 to something that kind of represents the table I am aliasing, you'll find this much easier especially when working with larger queries. More reading here: Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)