SQL convert column to comma separated row

Question

I am trying to combine UserName field into a comma separated string if the Name is the same. Current output:

Since Name = Admin has 4 users linked to it, I am trying to display as as admin@insi.co.za, miguel.reece8@gmail.com, mmm@test.com, test@test.com instead of it being in separate rows.

SQL query

SELECT DISTINCT
   Name,
   a3.UserName 
FROM
   PromotionTracker.dbo.AspNetRoles a1 
   JOIN
      PromotionTracker.dbo.AspNetUserRoles a2 
      ON a1.Id = a2.RoleId 
   JOIN
      PromotionTracker.dbo.AspNetUsers a3 
      ON a2.UserId = a3.Id

I tried using STUFF() function but it still gives me the same output not sure what is incorrect in the query.

Updated query with STUFF function

SELECT DISTINCT
   Name,
   STUFF((
   SELECT DISTINCT
      ',' + a3.UserName FOR XML PATH('') ), 1, 1, '' ) 
   FROM
      PromotionTracker.dbo.AspNetRoles a1 
      JOIN
         PromotionTracker.dbo.AspNetUserRoles a2 
         ON a1.Id = a2.RoleId 
      JOIN
         PromotionTracker.dbo.AspNetUsers a3 
         ON a2.UserId = a3.Id

Please tag the appropriate database that which you are using? — Teja Goud Kandula, Aug 20 '21 at 09:52
`string_agg()`? Docs: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15 — MatBailie, Aug 20 '21 at 10:00
Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Stu, Aug 20 '21 at 10:42

score 3 · Answer 1 · answered Aug 20 '21 at 11:06

SQL Server has supported string_agg() since SQL Server 2017. In your context that would be:

SELECT r.Name, STRING_AGG(u.UserName, ',') as usernames
FROM PromotionTracker.dbo.AspNetRoles r JOIN
     PromotionTracker.dbo.AspNetUserRoles ur
     ON r.Id = ur.RoleId JOIN
     PromotionTracker.dbo.AspNetUsers u
     ON ur.UserId = u.Id
GROUP BY r.Name;

Note that the meaningless table aliases have been replaced by aliases that represent the tables ("u" for users, for instance). Also, all column references are qualified, so it is clear what table they come from.

score 1 · Answer 2 · answered Aug 20 '21 at 09:58

1

Select Name, STRING_AGG(UserName,',') as GroupedUserNames from TableName group by Name

answered Aug 20 '21 at 09:58

Teja Goud Kandula

1,462
13
26

score 0 · Answer 3 · answered Aug 20 '21 at 12:32

0

Try this method:

select distinct stuff((
select ',' + a3.UserName
FROM dbo.AspNetRoles     a1
JOIN dbo.AspNetUserRoles a2 ON a1.Id = a2.RoleId
JOIN dbo.AspNetUsers     a3 ON a2.UserId = a3.Id
for xml path('')
),1,1,'')

answered Aug 20 '21 at 12:32

Lucas_Poland

48
10

SQL convert column to comma separated row

3 Answers3

Linked