Generate an HTML table from an SQL query

Sometimes it’s useful to be able to generate an HTML table based on the data produced by a query. I’ve worked with clients who have needed this done as part of an internal automated notification procedure, for example.

The technique relies on the FOR XML PATH T-SQL clause, which comes in handy in cases when you need to return results as a concatenated string.

USE AdventureWorks2017;
GO

SELECT
      (
          SELECT 'Sales Person Bonuses'
          FOR XML PATH(''), TYPE
      ) AS 'caption',
      (
          SELECT 'First Name' AS th,
                 'Last Name'  AS th,
                 'Bonus'      AS th
          FOR XML raw('tr'), ELEMENTS, TYPE
      ) AS 'thead',
      (
          SELECT p.FirstName AS td,
                 p.LastName AS td,
                 CAST(sp.Bonus AS DECIMAL(18, 2)) AS td
          FROM Sales.SalesPerson AS sp
              INNER JOIN Person.Person AS p ON p.BusinessEntityID = sp.BusinessEntityID
          ORDER BY sp.Bonus DESC
          FOR XML RAW('tr'), ELEMENTS, TYPE
      ) AS 'tbody'
FOR XML PATH(''), ROOT('table')

This query produces a single cell value with the HTML table definition all smushed together.

<table><caption>Sales Person Bonuses</caption><thead><tr><th>First Name</th><th>Last Name</th><th>Bonus</th></tr></thead><tbody><tr><td>Tsvi</td><td>Reiter</td><td>6700.00</td></tr><tr><td>Lynn</td><td>Tsoflias</td><td>5650.00</td></tr><tr><td>Jae</td><td>Pak</td><td>5150.00</td></tr><tr><td>Pamela</td><td>Ansman-Wolfe</td><td>5000.00</td></tr><tr><td>José</td><td>Saraiva</td><td>5000.00</td></tr><tr><td>Michael</td><td>Blythe</td><td>4100.00</td></tr><tr><td>Tete</td><td>Mensa-Annan</td><td>3900.00</td></tr><tr><td>Shu</td><td>Ito</td><td>3550.00</td></tr><tr><td>David</td><td>Campbell</td><td>3500.00</td></tr><tr><td>Jillian</td><td>Carson</td><td>2500.00</td></tr><tr><td>Linda</td><td>Mitchell</td><td>2000.00</td></tr><tr><td>Ranjit</td><td>Varkey Chudukatil</td><td>985.00</td></tr><tr><td>Garrett</td><td>Vargas</td><td>500.00</td></tr><tr><td>Rachel</td><td>Valdez</td><td>75.00</td></tr><tr><td>Stephen</td><td>Jiang</td><td>0.00</td></tr><tr><td>Syed</td><td>Abbas</td><td>0.00</td></tr><tr><td>Amy</td><td>Alberts</td><td>0.00</td></tr></tbody></table>

While the raw output is admittedly a little ugly, overall this can be a big time-saver. Here’s the end result when the output is rendered on a web page or in an email: