SQL Server Select With Loop on second table

Question

I am struggling with some SQL logic and wondered if you can help?

I need to get a list of ID's from table "Roles" where emailEnabled is true, I then need to use these IDs against the table "Users" on the roleid column to grab their email addresses and return these as a comma separated list.

I know we need to use a loop in here but it's not my strong point.

Let me know if you need any further info.

Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — sagi, Jun 09 '16 at 11:22

score 2 · Accepted Answer · answered Jun 09 '16 at 11:18

2

I would not recommend generating a comma seperated list in SQL. Grab the data you need record-wise and handle the rest in your program logic.

You can get the data you need like this

select users.email
from users
join roles on roles.id = users.roleid
where roles.emailEnabled = 1

answered Jun 09 '16 at 11:18

juergen d

201,996
37
293
362

Agreed, classic case of me trying to get to deep instead of keeping it simple :-) Will mark as accepted as soon as I can. – OPSL Jun 09 '16 at 11:24

score 0 · Answer 2 · answered Jun 09 '16 at 11:29

You could do this in SQL although I'd probably advise against it;

DECLARE @tmp varchar(100)
SET @tmp = ''
SELECT @tmp = COALESCE(@tmp + ',','') + (SELECT u.emailAddress FROM Roles r LEFT JOIN Users u ON r.roleid = u.roleid WHERE r.emailEnabled = 'true')

SELECT @tmp =  RIGHT(@tmp,LEN(@tmp)-1)

SQL Server Select With Loop on second table

2 Answers2