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] ASVARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td =CONVERT(VARCHAR(30),[StartDate],120) ,'',
td =CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDERBY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'

Solution 2:

createtable #table (
    a varchar(50),
    b varchar(50),
    c varchar(50)
)
insertinto #tablevalues
    ('xx','xxx','xxxx'),
    ('aaa','b','cc'),
    ('xxxx','xx','xx xxxx')
go
DECLARE@HTMLTableVARCHAR(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 #tableset@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.
)  
  ASBEGINSET NOCOUNT ON;

IF @orderByISNULLBEGINSET@orderBy=''ENDSET@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 @htmlEXEC  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'