In our database career as a developer or DBA, we get new challenges everyday. We get many things to research upon and apply them in real time. In starting days of my DBA career, one of my task was to run a query on the database and send the returned result set in excel format to general manager every morning without fail. It was a very small analytic report. My manager did not want to put this report inside the application and make user friendly because the query was pretty heavy and needed to run in off business hours only. As a junior DBA, this was my responsibility. One fine morning, I felt this task tedious and planned to automate the same. I did it by writing a small script to form a HTML table dynamically, replica of my SQL result set and scheduled the same in job scheduler to have it run in off business hours. I was appreciated by manager for the same. Find the below query which will dynamically add html table row and table data to form a HTML table.
DECLARE @table TABLE( [id] [varchar](100) NULL, [author] [varchar](100) NULL, [title] [varchar](100) NULL, [genre] [varchar](100) NULL, [price] [decimal](18, 2) NULL, [publish_date] [datetime] NULL, [description] [varchar](max) NULL) INSERT INTO @table SELECT N'bk101', N'Gambardella, Matthew', N'XML Developer''s Guide', N'1', CAST(44.95 AS decimal(18, 2)), CAST(0x00008FBE00000000 AS datetime), N'An in-depth look at creating applications.' UNION ALL SELECT N'bk102', N'Ralls, Kim', N'Midnight Rain', N'2', CAST(5.95 AS decimal(18, 2)), CAST(0x0000900A00000000 AS datetime), N'A former architect battles corporate zombies.' UNION ALL SELECT N'bk103', N'Corets, Eva', N'Maeve Ascendant', N'2', CAST(5.95 AS decimal(18, 2)), CAST(0x00008FED00000000 AS datetime), N'After the collapse of a nanotechnology.' UNION ALL SELECT N'bk104', N'Corets, Eva', N'Oberon''s Legacy', N'2', CAST(5.95 AS decimal(18, 2)), CAST(0x0000905E00000000 AS datetime), N' Sequel to Maeve Ascendant.' UNION ALL SELECT N'bk105', N'Corets, Eva', N'The Sundered Grail', N'2', NULL, CAST(0x0000911600000000 AS datetime), N'Sequel to Oberon''s Legacy.' SELECT * FROM @table DECLARE @tblTRTD varchar(max) = '' DECLARE @tblHD varchar(max) SET @tblTRTD = CAST(( SELECT id as 'td' , '', author as 'td' , '', title as 'td' , '', genre as 'td' , '', isnull(price,0.00) as 'td' , '', CONVERT(VARCHAR(30),publish_date,121) as 'td' , '', [description] as 'td' , '' FROM @table 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>BookID</th> <th>Author</th> <th>Title</th> <th>Genre</th> <th>Price</th> <th>PublishedDate</th> <th>Description</th> </tr> '+@tblTRTD+' </table> </body> </html>' SELECT @tblHD --Use http://htmledit.squarefree.com/ to test the html table