Duplicating rows caused by multiple email addresses

Question

Hello

I using SQL Server 2014.

I have two tables:

PersonInfo
EmailAd

which join on a reference number, so:

SELECT
Firstname, Lastname, refnumber, emailaddress
from PersonInfo PI
Left Join EmailAd EA on PI.Refnumber = EA.Refnumber

However some people have more than one email so the output is sometimes:

Firstname Lastname refnumber emailaddress

William Faulkner 1 will@xyz
William Faulkner 1 bill@xyz

and can be as many as 5 emails sometimes

My desired output would be:

Firstname Lastname refnumber emailaddress
William Faulkner 1 will@xyz , bill@xyz

basically a way of keeping all the data on one row, regardless of how many emails the person has in the system.

Please can you advise on a way I can achieve this?

Thanks

Answer 1

Select Distinct PI.Firstname, PI.Lastname, PI.refnumber,
  (Select Stuff(
    (Select ', ' + EA.emailaddress From EmailAd EA Where EA.refnumber = PI.refnumber 
    For XML Path(''),Type)
    .value('text()[1]','varchar(max)'),1,2,N'')) As emailaddress
From PersonInfo PI;

Tom