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

Generate Excel compatible XML File in SQL Server

create table tempXML
(
Id int identity(1,1),
ExportXml nvarchar(max)
)

create table #tempData
(
name nvarchar(100),
[Description] nvarchar(100),
Sku nvarchar(100)
)

insert #tempData( name, Description, Sku )
values  ( ‘Test01′,’Description’,’0000123456BLKMED’)

insert #tempData( name, Description,  Sku )
values  ( ‘Test02′,’Description’,’0000123456BLKLRG’)

insert #tempData( name, Description,  Sku )
values  ( ‘Test03′,’Description’,’0000123456BLKXLL’)

insert #tempData( name, Description,  Sku )
values  ( ‘Test04′,’Description’,’0000123456BLKSML’)

declare @XMLHeader nvarchar(max), @count int

select @count = count(*) from #tempData

Select @XMLHeader = ‘<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″&gt;
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<Author>MP</Author>
<LastAuthor>MP</LastAuthor>
<Created>’ + replace(convert(varchar,getdate(),102),’.’,’-‘) + ‘T’ + convert(varchar,getdate(),108) + ‘Z</Created>
<Company>Microsoft</Company>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns=”urn:schemas-microsoft-com:office:office”>
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<WindowHeight>7740</WindowHeight>
<WindowWidth>12435</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Borders/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”55″ ss:ExpandedRowCount=”‘ + convert(nvarchar,@count+1) + ‘” x:FullColumns=”1″ x:FullRows=”1″ ss:DefaultRowHeight=”15″>
<Column ss:Index=”6″ ss:AutoFitWidth=”0″ ss:Width=”69″/>’

insert tempXML
values (@XMLHeader)

insert tempXML
values (‘
<Row ss:AutoFitHeight=”0″>
<Cell><Data ss:Type=”String”>Name</Data></Cell>
<Cell><Data ss:Type=”String”>Description</Data></Cell>
<Cell><Data ss:Type=”String”>SKU</Data></Cell>
</Row>’
)

— data
insert tempXML
select
‘    <Row ss:AutoFitHeight=”0″>
<Cell><Data ss:Type=”String”>’ + coalesce([Name],”) + ‘</Data></Cell>’ + char(10) +
‘        <Cell><Data ss:Type=”String”>’ + coalesce([Description], ”) + ‘</Data></Cell>’ + char(10) +
‘        <Cell><Data ss:Type=”String”>’ + coalesce([SKU], ”) + ‘</Data></Cell>’ + char(10) +
‘   </Row>’
from #tempData

declare @XMLFooter nvarchar(max);

set @XMLFooter = ‘
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”0.3″/>
<Footer x:Margin=”0.3″/>
<PageMargins x:Bottom=”0.75″ x:Left=”0.7″ x:Right=”0.7″ x:Top=”0.75″/>
</PageSetup>
<Unsynced/>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
‘;

insert tempXML
values (@XMLFooter)

if object_id(‘tempdb.dbo.#errors’) is not null
drop table #errors

create table #errors (
ID int identity(1,1),
Logmsg nvarchar(max)
)

insert #errors(Logmsg)
exec master.sys.xp_cmdshell ‘DEL /F /Q “M:\Temp\Test.xml”‘

insert #errors(Logmsg)
exec master.sys.xp_cmdshell ‘bcp “select ExportXML from eur_support.dbo.tempXML order by ID” queryout “M:\Temp\Test.xml” -CRAW -w -T ‘

select * from tempXML
select * from #errors

drop table #tempData
drop table #errors
drop table tempXML

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