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)
5 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
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.
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.
hi i am looking for color codes depneds data data suppose firstname = ‘abc’ then red else green like
Hi Dave,
Can you help me in getting this working? I want to get the files <10 % to be highlighted RED in the received email but it seems to be only adding to the first column this?
Added to colour code the emailed results
SELECT '’ +
CASE WHEN CAST (REPLACE ([% Free File Space], ‘%’, ”) AS DECIMAL (15,2) ) ‘ +
This is the received column format
Server
bgcolor=”#FF0000″>
MYServerName
My code is =
SET NOCOUNT ON
CREATE TABLE #Temp
(
[Server] [varchar] (128) NULL,
[Database] [varchar] (128) NULL,
[File Name] [sys].[sysname] NOT NULL,
[Type] [varchar] (60) NULL,
[Path] [varchar] (260) NULL,
[File Size] [varchar] (53) NULL,
[File Used Space] [varchar] (53) NULL,
[File Free Space] [varchar] (53) NULL,
[% Free File Space] [varchar] (51) NULL,
[Autogrowth] [varchar] (53) NULL,
[volume_mount_point] [varchar] (256) NULL,
[Total Volume Size] [varchar] (53) NULL,
[Free Space] [varchar] (53) NULL,
[% Free] [varchar] (51) NULL
)
EXEC sp_MSforeachdb ‘ USE [?];
INSERT INTO #Temp
SELECT @@SERVERNAME [Server] ,
DB_NAME() [Database] ,
MF.name [File Name] ,
MF.type_desc [Type] ,
MF.physical_name [Path] ,
CAST(CAST(MF.size / 128.0 AS DECIMAL(15, 2)) AS VARCHAR(50)) + ” MB” [File Size] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 – ( ( size / 128.0 ) – CAST(FILEPROPERTY(MF.name, ”SPACEUSED”) AS INT) / 128.0 )) AS VARCHAR(50)) + ” MB” [File Used Space] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 – CAST(FILEPROPERTY(MF.name, ”SPACEUSED”) AS INT) / 128.0) AS VARCHAR(50)) + ” MB” [File Free Space] ,
CAST(CONVERT(DECIMAL(10, 2), ( ( MF.size / 128.0 – CAST(FILEPROPERTY(MF.name, ”SPACEUSED”) AS INT) / 128.0 ) / ( MF.size / 128.0 ) ) * 100) AS VARCHAR(50)) + ”%” [% Free File Space] ,
IIF(MF.growth = 0, ”N/A”, CASE WHEN MF.is_percent_growth = 1 THEN CAST(MF.growth AS VARCHAR(50)) + ”%”
ELSE CAST(MF.growth / 128 AS VARCHAR(50))
+ ” MB”
END) [Autogrowth] ,
VS.volume_mount_point ,
CAST(CAST(VS.total_bytes / 1024 / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ ” GB” [Total Volume Size] ,
CAST(CAST(VS.available_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ ” GB” [Free Space] ,
CAST(CAST(VS.available_bytes / CAST(VS.total_bytes AS DECIMAL(20, 2))
* 100 AS DECIMAL(20, 2)) AS VARCHAR(50)) + ”%” [% Free]
FROM sys.database_files MF
CROSS APPLY sys.dm_os_volume_stats(DB_ID(”?”), MF.file_id) VS
‘
SELECT ” +
CASE WHEN CAST (REPLACE ([% Free File Space], ‘%’, ”) AS DECIMAL (15,2) ) ‘ +
” + [Server] + ” +
” + [Database] + ” +
” + [File Name] + ” +
” + Type + ” +
” + Path + ” +
” + [File Size] + ” +
” + ISNULL([File Used Space], ‘N/A’) + ” +
” + ISNULL([File Free Space], ‘N/A’) + ” +
” + ISNULL([% Free File Space], ‘N/A’) + ” +
” + Autogrowth + ” +
” + volume_mount_point + ” +
” + [Total Volume Size] + ” +
” + [Free Space] + ” +
” + [% Free] + ” +
”
FROM #Temp
DROP TABLE #Temp
GO