SQL Server : Combine two rows based on the values of one column

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 a table with players from various teams and their email addresses. PlayerEmails

The task is to create a mailing list for each team so that it returns the result belowEmailResult

Here is the script to create the table, insert sample data and the query to select the mailing lists.

PlayerEmailScript

Here is the code text if you want to copy and paste:

create table PlayerEmails
(
Team varchar(100),
Player varchar(100),
Email varchar(200),
)
go
insert PlayerEmails (Team,Player,Email)
select 'SL','Mahela', 'Mahela@email.com'
union
select 'SL','Sanga', 'Sanga@email.com'
union
select 'SL','Dilshan', 'Dilshan@email.com'
union
select 'WI','Gayle', 'Gayle@email.com'
union
select 'Aus','Waugh', 'Waugh@email.com'
union
select 'Aus','Warne', 'Warne@email.com'
go
select    Team,
replace(
(    select Email as [data()]
from    PlayerEmails t2
where    t2.Team = t1.Team
for xml path('')
)
, ' ', ';'
) as EmailAddress
from    PlayerEmails t1
group by Team
go
drop table PlayerEmails
go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s