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
Now copy result and open notepad, paste result content and save it as .html file.