Alternate Row Colors in an HTML Table Using T-SQL

For alternate row colors in an HTML table, number the rows and choose a color by odd or even. T-SQL can do both in one query, and the result is HTML you can save, serve or email.

The Idea in Two Lines

ROW_NUMBER() gives every row a position. The remainder of that position divided by two is 0 for even rows and 1 for odd rows. A CASE turns the remainder into a color. That’s the whole trick, and the rest of the work is building the HTML around it.

The demo uses a small juice shop table with five rows. Two names contain characters that HTML treats as code, an ampersand and angle brackets. They test the escaping later.

SET NOCOUNT ON;
IF DB_ID(N'HtmlRowColorDemo') IS NULL CREATE DATABASE HtmlRowColorDemo;
GO
USE HtmlRowColorDemo;
GO
DROP TABLE IF EXISTS dbo.Juices;
CREATE TABLE dbo.Juices (
    JuiceID   int          NOT NULL PRIMARY KEY,
    JuiceName nvarchar(60) NOT NULL,
    Price     decimal(5,2) NOT NULL,
    StockQty  int          NOT NULL
);
INSERT INTO dbo.Juices (JuiceID, JuiceName, Price, StockQty)
VALUES (1, N'Apple', 3.50, 42),
       (2, N'Carrot & Ginger', 4.25, 6),
       (3, N'Mango', 4.00, 18),
       (4, N'Orange <fresh>', 3.75, 3),
       (5, N'Pineapple', 4.50, 25);

Build the Table With STRING_AGG

This query builds alternate row colors in three steps. The first CTE numbers the rows in the order you want to see them. The second builds one <tr> string for each row. The final SELECT joins the strings with STRING_AGG and wraps them in a table with a header.

WITH numbered AS (
    SELECT JuiceName, Price, StockQty,
           ROW_NUMBER() OVER (ORDER BY JuiceName) AS rn
    FROM dbo.Juices
), html AS (
    SELECT rn,
           N'<tr style="background:' + CASE WHEN rn % 2 = 0 THEN N'#eef2f6' ELSE N'#ffffff' END + N'">'
         + N'<td>' + REPLACE(REPLACE(REPLACE(JuiceName, N'&', N'&amp;'), N'<', N'&lt;'), N'>', N'&gt;') + N'</td>'
         + N'<td style="text-align:right">' + CONVERT(nvarchar(20), Price) + N'</td>'
         + N'<td style="text-align:right' + CASE WHEN StockQty < 10 THEN N';color:#b00020;font-weight:bold' ELSE N'' END + N'">'
         + CONVERT(nvarchar(20), StockQty) + N'</td></tr>' AS row_html
    FROM numbered
)
SELECT N'<table style="border-collapse:collapse;width:100%">' + CHAR(10)
     + N'<tr style="background:#1f2937;color:#ffffff"><th>Juice</th><th>Price</th><th>Stock</th></tr>' + CHAR(10)
     + STRING_AGG(CONVERT(nvarchar(max), row_html), CHAR(10)) WITHIN GROUP (ORDER BY rn) + CHAR(10)
     + N'</table>' AS HtmlTable
FROM html;

The numbering lives in its own CTE because a window function can’t sit inside an aggregate. STRING_AGG needs SQL Server 2017 or later. Its WITHIN GROUP (ORDER BY rn) keeps the rows in the order of the numbers. The stripes can’t drift.

The Output

The grid holds one long value. Here it is, broken into lines by the CHAR(10) separators. The next block shows the value. It is output, not code to run.

<table style="border-collapse:collapse;width:100%">
<tr style="background:#1f2937;color:#ffffff"><th>Juice</th><th>Price</th><th>Stock</th></tr>
<tr style="background:#ffffff"><td>Apple</td><td style="text-align:right">3.50</td><td style="text-align:right">42</td></tr>
<tr style="background:#eef2f6"><td>Carrot &amp; Ginger</td><td style="text-align:right">4.25</td><td style="text-align:right;color:#b00020;font-weight:bold">6</td></tr>
<tr style="background:#ffffff"><td>Mango</td><td style="text-align:right">4.00</td><td style="text-align:right">18</td></tr>
<tr style="background:#eef2f6"><td>Orange &lt;fresh&gt;</td><td style="text-align:right">3.75</td><td style="text-align:right;color:#b00020;font-weight:bold">3</td></tr>
<tr style="background:#ffffff"><td>Pineapple</td><td style="text-align:right">4.50</td><td style="text-align:right">25</td></tr>
</table>

Save that text in a file ending in .html and open it in a browser. The rows alternate white and grey. Three details deserve a look.

The two odd names were escaped. Carrot & Ginger and Orange <fresh> display as typed instead of breaking the page.

The stock cells for 6 and 3 are red and bold. A second CASE colors a cell by its value. Put that CASE inside the style attribute of the cell you want colored, as the Stock cell does. And every color is an inline style on its element, so the table carries its look wherever the HTML goes.

Why the Numbering Follows the Display Order

The ROW_NUMBER() order and the final order must be the same. Here both use the juice name. The old script of this kind numbered the rows in descending order and displayed them in ascending order. The stripes still alternated, but the color of the first row depended on how many rows came back.

A constant in the numbering is worse. The stripes then follow whatever order the engine reads, and SQL Server promises no order without an ORDER BY. Order by a real column and the alternate row colors stay stable.

Older versions of this query built the string by assigning a variable inside a multi-row SELECT. That pattern isn’t guaranteed to concatenate rows in order, and STRING_AGG replaces it with a documented one.

The CSS Alternative

A browser can stripe a table without any help from T-SQL. A rule such as tr:nth-child(even) in a style block picks every second row. If the HTML ends up only on a web page you control, that is simpler. The query then needs no row numbers.

The T-SQL way earns its place when the HTML leaves your control, such as an email or a saved file. Inline styles travel with each row. They also let the query color a row by its data, not by its position. A CASE on stock level, a status or a percentage works the same way as the odd and even test.

Use It in a File or an Email

You could argue that this formatting belongs in the application, not in T-SQL. For a real website it does. For an alert email from a scheduled job, T-SQL is the tool at hand. Pass the result to sp_send_dbmail with @body_format = 'HTML' and the table arrives as a table.

For a long result, don’t copy the value from the grid. SSMS cuts a character value at 65,535 characters by default. Send the output to a file, or raise the limit in the query options, so the closing </table> isn’t lost.

What to Remember

Number the rows from the same ORDER BY you display. Escape the text, and put the color on each element. Then alternate row colors come out the same on every run. Run the cleanup script when you finish.

USE master;
GO
IF DB_ID(N'HtmlRowColorDemo') IS NOT NULL
BEGIN
    ALTER DATABASE HtmlRowColorDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE HtmlRowColorDemo;
END;

A striped table is not decoration, it is a row number with a color.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.