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:

01DECLARE @tblTRTD varchar(max) = ''
02DECLARE @tblHD varchar(max)
03SET @tblTRTD = CAST((
04SELECT
05empid as 'td' , '',
06firstname  as 'td' , '',
07lastname as 'td' , '',
08address as 'td' , '',
09city as 'td' , '',
10state   as 'td' , ''
11 
12FROM dbo.emp FOR XML PATH('tr')) AS VARCHAR(MAX))
13  
14SET @tblHD = '<!DOCTYPE html>
15<html>
16<table border="1" width="50%" cellpadding="5" cellspacing="1">
17<tr>
18<th colspan="7"><br>TEST HTML TABLE GENERATED FROM SQL SERVER</th>
19</tr>
20<tr bgcolor="lightgrey">
21<th>EmployeeID</th>
22<th>Firstname</th>
23<th>Lastname</th>
24<th>Address</th>
25<th>City</th>
26<th>State</th>
27</tr>
28'+@tblTRTD+'
29</table>
30</body>
31</html>'
32SELECT @tblHD

Result: 

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

SQL Query Result set to Html Table