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.

SQL SERVER - Alternate Rows Color in Table Background in HTML rowcolor-800x574

Solarwinds

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.

SQL SERVER - Alternate Rows Color in Table Background in HTML html1

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.

SQL SERVER - Alternate Rows Color in Table Background in HTML html2

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Save Auto Recover Information – SSMS
Next Post
SQL SERVER – Unable to Start SQL Resource in Cluster – HUGE Master Database!

Related Posts

3 Comments. Leave new

  • Hi Pinal,

    Is there a reason for sorting the ROW_NUMBER() descedning and whould it aprove performance if one replaces
    ROW_NUMBER() OVER (ORDER BY [BusinessEntityID] DESC)
    by
    ROW_NUMBER() OVER (ORDER BY (SELECT 1))?

    Greetz
    Kees de Boer

    Reply
  • Sir, i have a html output from sql :- select ‘ “‘ + ‘

    line 1
    line 2

    ‘ +’ “‘
    output comes like this:- ”

    line 1
    line 2


    when i try to copy and paste this in excel i get value in two single cell. Is there any way to bring the output from sql to excel in a single line for the unordered list html output getting from sql result set into single cell in excel column.

    Reply
  • Sir, I have query which result me html output : -select ‘”

    line 1
    line 2

    ” ‘ while I copy and paste the result set in excel it displays in two different cell. What should I do so that result set output values while copy and paste in excel gives me in a single cell instead of two cells.

    Reply

Leave a Reply

Menu