asked on

Controlling the format of individual columns in a query using sp_send_dbmail

I have written a simple query to email data using sp_send_dbmail as per the example in Experts Exchange.
I use style sheets to control the format and this works fine for all columns returned using the style for td but I have a need to control the format of individual columns of data only e.g. control the text alignment/font size/background colour etc of the value column data independently of the column header.
Can this be done? If so how please.

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
N'

<head>
      <STYLE TYPE="text/css">

p.title{font-family:arial;font-size:12pt;color:blue;font:bold}
th{font-family:arial;font-size:10pt;color:white;background-color:blue;text-align:left;padding-right:10px}
td{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black}
</STYLE>
</head>
      <p class="title">Sales Report UK</p>
<table>
<tr>
<th>Period</th>
<th>Posting Date</th>
<th>Salesperson</th>
<th>Ship To Customer</th>
<th>Item.....</th>
<th>Description</th>
<th>Qty</th>
<th>Value (£)</th></tr>' +
CAST ( ( SELECT td = Period,'',
                              td = [Posting Date],'',
td = Salesperson, '',
td = Customer, '',
td = Item, '',
td = Description, '',
td = Qty, '',
td = [Line Val (LCY)]
FROM [$ UK Sales Analysis] AS SA
WHERE [Product Series]='ProductA'
ORDER BY Period,[Posting Date],Salesperson,Customer
--GROUP BY Period
--ORDER BY [Product Series]

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
<p class="title">Sales Report France</p>
<table class="table">
<tr class="header">
<th>Period</th>
<th>Posting Date</th>
<th>Salesperson</th>
<th>Ship To Customer</th>
<th>Item</th>
<th>Description</th>
<th>Qty</th>
<th>Value(€)</th></tr>' +
CAST ( ( SELECT td = Period,'',
                              td = [Posting Date],'',
td = Salesperson,'',
td = Customer,'',
td = Item,'',
td = Description,'',
td = Qty,'',
td = [Line Val (€)]
FROM [$ FR Sales Analysis] AS SA
WHERE [Product Series]='ProductA'
ORDER BY Period,[Posting Date],Salesperson,Customer
--GROUP BY Period
--ORDER BY [Product Series]

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table> '

EXEC msdb.dbo.sp_send_dbmail
      @recipients='xxx@yyy.com',
@subject = 'Sales Report',
@body = @tableHTML,
@body_format = 'HTML',
      @profile_name ='SQL'

ASKER CERTIFIED SOLUTION

Ztinel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Purdham

ASKER

Brilliant. Thanks.
Watch out for the next question following shortly.