Categories
Sql Server

SQL Query Result set to Html Table

SQL Query Result set to Html Table

In this post we will discuss how to get SQL Query Result set to Html Table. Getting SQL query results to a html is very useful. It is very useful for reporting purpose. In my day to day activities as SQL Sever DBA i have to send a report in Excel format to my Project manager, their is built on feature on SQL Server to export SQL Query result to Result to text, Result to Grid and Result to File. But these are not useful to me as result to file option export data to .rpt format.

To achieve this we used below script:

DECLARE @tblTRTD varchar(max) = ''
DECLARE @tblHD varchar(max)
SET @tblTRTD = CAST((
SELECT
empid as 'td' , '',
firstname  as 'td' , '',
lastname as 'td' , '',
address as 'td' , '',
city as 'td' , '',
state   as 'td' , ''

FROM dbo.emp FOR XML PATH('tr')) AS VARCHAR(MAX))
 
SET @tblHD = '<!DOCTYPE html>
<html>
<table border="1" width="50%" cellpadding="5" cellspacing="1">
<tr>
<th colspan="7"><br>TEST HTML TABLE GENERATED FROM SQL SERVER</th>
</tr>
<tr bgcolor="lightgrey">
<th>EmployeeID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th>City</th>
<th>State</th>
</tr>
'+@tblTRTD+'
</table>
</body>
</html>'
SELECT @tblHD

Result: 

Now copy result and open notepad, paste result content and save it as .html file.

SQL Query Result set to Html Table