I'm new to SQL (running SQL Server 2012), the query I'm running is returning these results.
IDNum Email
----------------
1 [email protected]
1 [email protected]
1 [email protected]
2 [email protected]
2 [email protected]
3 [email protected]
3 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
5 [email protected]
I would like to get the following result set (a comma separated list unique to each id)
IDNum Emails
---------------------------------
1 [email protected],[email protected],[email protected]
2 [email protected],[email protected]
3 [email protected],[email protected],[email protected]
4 [email protected]
5 [email protected],[email protected]
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
