I'm using SQL Server 2005 and am sending a Database Mail formatted in HTML. I am getting the results I need, but I'm having difficulty formatting the table the way I want it.
There are two separate SELECT statements being run that populate the table rows. The problem is that I want them to display side by side as they are in relation to each other, but they will only display one on top of the other. I have tried putting them into two tables within a larger table but it will not shift it over. I haven't used HTML in about 10 years so it's probably a problem with that more than the script itself.
Here is the @body section that contains the two select statements:
N'<table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
N'<th>Store Number</th>' +
N'<td>'+ CAST ( ( SELECT store_num
FROM store_results
WHERE successful = 'N'
OR successful IS NULL
ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )+'</td>'+
N'</td>' +
N'<td><table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
N'<th>Reason</th>' +
N'<td>'+CAST ( ( SELECT
CASE successful
WHEN 'N' THEN 'Failed'
ELSE 'Did Not Run'
END
FROM store_results where successful = 'N' OR successful is null
ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )+'</td>'+
N'</td>' +
N'</table>
I finally figured this out. You have to set up the header TDs first to create the columns. Instead of using just format the to have it look how you want. Like so:
N'<table border="1" cellpadding="0" cellspacing="0"><font face="arial">' +
N'<tr><td>Store Number</td><td>Reason</td></tr>' +
N'<td>'+ CAST ( ( SELECT
td = store_num
td = reason
from store_results
ORDER BY store_num ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
N'</table>
You can actually format your cells based on the result of the query by sticking in td/@attribute = case... if you wanted to get fancy with it. I ended up putting all my results into a temp table earlier in the sp to keep the html generation code simpler.