I came across a requirement to combine rows based on the values of a column. This is an interesting query so thought of sharing it with a simple example online.
Here is the script to create the table, insert sample data and the query to select the mailing lists.
Here is the code text if you want to copy and paste:
create table PlayerEmails
insert PlayerEmails (Team,Player,Email)
select 'SL','Mahela', 'Mahela@email.com'
select 'SL','Sanga', 'Sanga@email.com'
select 'SL','Dilshan', 'Dilshan@email.com'
select 'WI','Gayle', 'Gayle@email.com'
select 'Aus','Waugh', 'Waugh@email.com'
select 'Aus','Warne', 'Warne@email.com'
( select Email as [data()]
from PlayerEmails t2
where t2.Team = t1.Team
for xml path('')
, ' ', ';'
) as EmailAddress
from PlayerEmails t1
group by Team
drop table PlayerEmails