Coalesce records in sql server when repeated two 2 times

Question

I have records as follow in a tables on sql server 2005

fname  lname  address  zip 
xxx    yyy     UK      001 
zzz    yyy     UK      001 
aaa    yyy     UK      002  
ddd    jjj     US      003 
eee    jjj     US      003 

I need to get the result in the following format

fname    lname  address  zip
xxx,zzz   yyy    UK      001
ddd,eee   jjj    US      003

Basically every records which have a count address and zip 2 times will have their first name grouped and separated by comma.

Ok Here is my approach: but not working and stuck right now

select fname, lname, address, zip from table people 
where address is not null
and zip is not null
group by address,zip 
having count(address)=2 and count (zip)=2 
order by address

-- Now to coalesce the records I am using

SELECT fname = COALESCE(fname + ', ', '') + ISNULL(fname, 'N/A'), fname, lname,streetname, housenumber
FROM people 
WHERE address is not null and zip is not null  
group by address,zip 
having count(address)=2 and count (zip)=2 
order by address

Answer 1

I don't think this is a duplicate because it doesn't require anything like group_concat(). The OP is specifically asking for two times, and you can get that like this:

select min(fname) + ',' + max(fname), lname, address, zip
from table t
group by lname, address, zip
having count(*) = 2;

Of course, a general answer with more matching rows can't be solved this way, but the question specifically says "zip 2 times".