How Do I Convert A Select Statement Result Into An HTML Table In SQL Server?

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) @HTML

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

Solution 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

Solution 3:

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'