I'm new to SQL (running SQL Server 2012), the query I'm running is returning these results.
IDNum Email
----------------
1 a@a.com
1 b@b.com
1 c@c.com
2 d@d.com
2 e@e.com
3 f@f.com
3 g@g.com
3 h@h.com
4 i@i.com
5 j@j.com
5 k@k.com
I would like to get the following result set (a comma separated list unique to each id)
IDNum Emails
---------------------------------
1 a@a.com,b@b.com,c@c.com
2 d@d.com,e@e.com
3 f@f.com,g@g.com,h@h.com
4 i@i.com
5 j@j.com,k@k.com
I've been trying to follow some of the answers from other questions but not having any luck. I'm sure it's some combination of my inexperience & all the other questions I'm finding with this are just results from a single table. My query is getting results from multiple tables if that makes a difference, would be similar to
SELECT DISTINCT
s.idnum, e.email
FROM
student s
JOIN
email e ON e.guid = s.guid
WHERE
s.activeYear = 1 AND e.activeEmail = 1
Can anyone help? Thanks.
******UPDATE******
I ended up using the following query after reading a few more articles here and on another website. Hope this helps someone in the future.
USE databaseName
SELECT s.idnum,
STUFF(( SELECT ',' + e.email AS [text()]
FROM email e
WHERE
e.guid = s.guid AND e.activeEmail = 1
FOR XML PATH('')
), 1, 1, '' )
AS emails
FROM student s
WHERE s.activeYear = 1
