SQL SERVER – Alternate Rows Color in Table Background in HTML

Have you ever felt when you get hired to do a job and your customers ask you question, which you would have not imagined in your wildest dream? Well, just happened to me yesterday where I was asked how to write T-SQL to show alternate rows color in table background in HTML.

Before I become Database Developer, I was an HTML developer (please note during that time HTML was the most happening thing out there and CSS was just about to begin its career). I think after 14-16 years, I got once again an opportunity to write HTML.

Here is the script which will generate HTML table with alternate rows color in table background.

 DECLARE @TableRows VARCHAR(MAX)
SET @TableRows = '<table border="1" width="100%">' +
'<tr bgcolor="black" style="font-weight:bold;color:white">
<td>Title</td><td>FirstName</td><td>LastName</td>
</tr>'
SELECT
@TableRows = @TableRows+'<tr ' + 'bgcolor='
+ IIF( ROW_NUMBER() OVER (ORDER BY [BusinessEntityID] DESC) % 2 = 0 , 'lightgrey' , 'white' ) +'>'
+'<td>' + CAST ([BusinessEntityID] AS VARCHAR(100)) + '</td>'
+ '<td>' + CAST ([FirstName] AS VARCHAR(100)) + '</td>'
+ '<td>' + CAST ([LastName] AS VARCHAR(100)) + '</td>'
+ '</tr>'
FROM [AdventureWorks2014].[Person].[Person]
WHERE [BusinessEntityID] < 10
ORDER BY [BusinessEntityID]
SELECT @TableRows + '</table>' AS SaveThisFileAsHTML

Once you run above script, you will find following resultset.

Save the resultset in the file with extension HTML. Once you do that, you can open the file in any browser like FireFox, Safari or Chrome. Though most of the time, the query returns good results in Microsoft’s browsers, there is no guarantee of it.

If you use any other trick to display Alternate Rows Color in Table Background in HTML, please post in the comments section and I will publish on the blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)