Formatting @query results using xp_sendmail

[Terri]

I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @MessageSubject VARCHAR(50)
SELECT @MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'm*@mine.com',
@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@subject = @MessageSubject

The results in my email look like:

au_fname au_lname
------------------- ----------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks

[Simon Hayes]

"Terri" <Te***@spamaway.com> wrote in message
news:c0**********@reader2.nmix.net...

I know formatting should be handled in the client app and not in SQL Server but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @MessageSubject VARCHAR(50)
SELECT @MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'm*@mine.com',
@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@subject = @MessageSubject

The results in my email look like:

au_fname au_lname
------------------- ----------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks

See CHAR() in Books Online - you could try something like this:

select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.authors

Simon

[Terri]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

See CHAR() in Books Online - you could try something like this:

select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.authors

This works, thanks.
@query = 'select ''First Name: '' + au_fname + char(13) + ''LastName: '' +
au_lname + char(13) from pubs.dbo.authors',