how do I convert a select statement result into an HTML table in SQL Server?

Question

I have a table:

Select A, B, C 
FROM Table
WHERE Z = P 
AND Y = N

I want to capture the results and create an HTML table out of it, as such:

DECLARE @HTMLTable VARCHAR(MAX)

@HTMLTable = ???

A   B   C
xx  xxx xxxxx
x   xx  x
xx  x   xxx

And so on.

The reason for this is because I want to send this HTML table in an email.

How do I go about doing this?

Answer 1

Make use of FOR XML PATH (learned from others here). something like this:

SET @tableHTML =
N'<table>' +
N'<tr><th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th>
</tr>' +
CAST ( (
SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'