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?

I know how to send an email... I'm trying to figure out how to convert a SQL result set to an HTML table — JJ., Oct 31 '13 at 16:06

score 9 · Accepted Answer · answered Oct 31 '13 at 16:34

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>'

It puts all the content (for a row) inside a single if I remove ,'', would you please help me understand why, if you know? — Lzh, Jun 12 '14 at 06:31

dav1dsm1th · Answer 2 · 2023-05-19T07:51:20.080

2

create table #table (
    a varchar(50),
    b varchar(50),
    c varchar(50)
)
insert into #table values
    ('xx','xxx','xxxx'),
    ('aaa','b','cc'),
    ('xxxx','xx','xx xxxx')
go
DECLARE @HTMLTable VARCHAR(MAX)

set @HTMLTable = '<table><thead><tr><th>a</th><th>b</th><th>c</th></tr></thead><tbody>'

select @HTMLTable += '<tr><td>'+a+'</<td><td>'+b+'</<td><td>'+c+'</<td></tr>'
from #table

set @HTMLTable += '</tbody></table>'

print @HTMLTable

edited May 19 '23 at 07:51

answered Oct 31 '13 at 16:31

dav1dsm1th

1,687
2
20
24

1

Example works well, this is the simplest and most straight forwards solution in my opinion. Only change recommended is to wrap the column values in an ISNULL(a, '') in the main SELECT. Otherwise any null value will cause the entire select to return nothing. – EkoostikMartin Jan 10 '19 at 19:43
1

I like the solution. It is simple yet allows some creativity in building the html. In my case I added an 'a href' tag in a td cell as I wanted recipients to be able to click on a link to go to a web page. – j.hull Nov 17 '22 at 14:39

score 0 · Answer 3 · answered Sep 01 '18 at 11:06

Create below procedure which will convert table result into html format

   create proc [dbo].[Proc_QueryToHtmlTable] (
@query NVARCHAR(MAX)
,--A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy NVARCHAR(MAX) = NULL
,--An optional ORDER BY clause. It should contain the words 'ORDER BY'.
@html NVARCHAR(MAX) = NULL OUTPUT --The HTML output of the procedure.
)  
  AS
  BEGIN
    SET NOCOUNT ON;

IF @orderBy IS NULL
BEGIN
    SET @orderBy = ''
END

SET @orderBy = REPLACE(@orderBy, '''', '''''');

DECLARE @realQuery NVARCHAR(MAX) = '
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);    

SELECT * INTO #dynSql FROM (' + @query + ') sub;

SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns 
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;

SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
FROM tempdb.sys.columns 
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;

SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
';

EXEC sys.sp_executesql @realQuery
    ,N'@html nvarchar(MAX) OUTPUT'
    ,@html = @html OUTPUT
 END

and then test as below

      declare @html NVARCHAR(MAX)=''
      exec Proc_QueryToHtmlTable '
     SELECT 1 as id, 2 as name  ',' name' ,@html OUTPUT
       print @html

    EXEC  msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@execute_query_database = 'Db NAME'
    ,@body = @html
    ,@body_format = 'HTML'
    ,@recipients = '***@gmail.com;**@gmail.com'
    ,@subject = 'test  Report'

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

3 Answers3

Linked