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

XML : Another example of generating XML

declare @branch_code nchar(3), @branch_id int, @tillnum int, @i int

select @branch_code = N’101′, @branch_id = 3, @tillnum = 1, @i = 10

set @xml = ‘<sale_or_return_ordering>
</sale_or_return_ordering>’
— key
select @key =
(
select   @branch_code as branch_code,
@branch_id as branch_id,
order_number,
@tillnum as tillnum
from  #Orders [key]
where seq = @i
for xml auto
)

— security
set @security = ‘<security username=”sa” />’

— preferences
set @preferences = ‘<preferences prefstylecode=”PROD_CODE” prefskucode=”SKU_CODE” prefstylecodeindex=”1″ prefskucodeindex=”1″ />’

set @xml.modify(‘insert sql:variable(“@preferences”) as first into (/sale_or_return_ordering)[1]’)

set @xml.modify(‘insert sql:variable(“@security”) as first into (/sale_or_return_ordering)[1]’)

set @xml.modify(‘insert sql:variable(“@key”) as first into (/sale_or_return_ordering)[1]’)

select @xml

XML : Read Values from XML in SQL Server

select  orders.item.value(N’@description’, ‘nvarchar(1000)’) as description ,
                        orders.item.value(N’@quantiy_ordered’,
                                          ‘nvarchar(1000)’) as quantiy_ordered ,
                        orders.item.value(N’@sizes_description_short’,
                                          ‘nvarchar(1000)’) as sizes_description_short ,
                        orders.item.value(N’@sku_code_composite’,
                                          ‘nvarchar(30)’) as sku_code_composite ,
                        orders.item.value(N’@selling_price’, ‘money’) as selling_price ,
                        orders.item.value(N’@quantiy_ordered’, ‘int’) as quantiy_ordered ,
                        orders.item.value(N’@quantity_short_picked’, ‘int’) as quantity_short_picked ,
                        orders.item.value(N’@epos_rr_units_sold’, ‘int’) as epos_rr_units_sold ,
                        orders.item.value(N’@quantity_returned’, ‘int’) as quantity_returned ,
                        orders.item.value(N’@quantity_adjustment’, ‘int’) as quantity_adjustment ,
                        orders.item.value(N’@quantity_balance’, ‘int’) as quantity_balance ,
                        orders.item.value(N’@pb0′, ‘int’) as pb0 ,
                        orders.item.value(N’@pb1′, ‘int’) as pb1 ,
                        orders.item.value(N’@pb2′, ‘nvarchar(100)’) as pb2
                from    @xml.nodes(‘/sale_or_return_ordering/list_items/row’) orders ( item );

XML : Insert an element into XML in SQL Server

declare @xml xml, @xml1 xml

set @xml1 =

<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”200″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”10″ />
</list_items>

set @xml =

<sale_or_return_ordering>
<key branch_code=”001″ />
<header module_state=”CLOSED” account_number=”0000578″ />
<list_billing_address>
<row billing_address=”MR” pb0=”Title” pb1=”1″ />
<row billing_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row billing_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row billing_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row billing_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
</list_billing_address>
</sale_or_return_ordering>

set @xml.modify(‘insert sql:variable(“@xml1”) as first into (/sale_or_return_ordering)[1]’)

select @xml

XML : Remove an element from xml in SQL Server

declare @xml xml

set @xml =

<sale_or_return_ordering>
<key branch_code=”001″ />
<header module_state=”CLOSED” account_number=”0000578″ />
<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”100″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”1″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010026003014″ selling_price=”-50.00″ quantiy_ordered=”1″ />
</list_items>
<list_billing_address>
<row billing_address=”MR” pb0=”Title” pb1=”1″ />
<row billing_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row billing_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row billing_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row billing_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
</list_billing_address>
</sale_or_return_ordering>

set @xml.modify(‘delete sale_or_return_ordering/list_items/row’)

select @xml

XML : Replace child xml elements with new child xml elements in SQL Server

— Replace all child elements of an xml element with new child elements

declare @xml xml, @xml1 xml

set @xml1 =

<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”200″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”10″ />
</list_items>

set @xml =

<sale_or_return_ordering>
<key branch_code=”001″ />
<header module_state=”CLOSED” account_number=”0000578″ />
<list_items>
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010035033030″ selling_price=”44.00″ quantiy_ordered=”100″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010111010006045014″ selling_price=”100.00″ quantiy_ordered=”1″ />
<row description=”Berghaus Mens Explorer Sport X” colour_description_short=”NAVY” sizes_description_short=”42″ sku_code_composite=”010112010026003014″ selling_price=”-50.00″ quantiy_ordered=”1″ />
</list_items>
<list_billing_address>
<row billing_address=”MR” pb0=”Title” pb1=”1″ />
<row billing_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row billing_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row billing_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row billing_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
</list_billing_address>
<list_delivery_address>
<row delivery_address=”MR” pb0=”Title” pb1=”1″ />
<row delivery_address=”Hannah” pb0=”First Name” pb1=”2″ />
<row delivery_address=”Palmer” pb0=”Surname” pb1=”3″ />
<row delivery_address=”50″ pb0=”Address line 1″ pb1=”4″ />
<row delivery_address=”Ward Road” pb0=”Address line 2″ pb1=”5″ />
<row delivery_address=”hannah23@gmail.co.uk” pb0=”EMAIL” pb1=”13″ />
</list_delivery_address>
</sale_or_return_ordering>

set @xml.modify(‘delete sale_or_return_ordering/list_items/row’)
set @xml.modify(‘insert sql:variable(“@xml1”) as last into (/sale_or_return_ordering/list_items)[1]’)

select @xml