SQL SERVER| Alternate rows color in email using SQL?

In the puzzle you have to email a table’s data but each row should have alternate backgroud color. For more details please refer sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE Employee1
(
	 Id INT
	,Emp VARCHAR(10)
)
GO

INSERT INTO Employee1 VALUES 
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan'),
(1,'Pawan')
GO

SELECT * FROM Employee1
GO


--

SOLUTION

--

DECLARE @HTML NVARCHAR(MAX) = ''
IF EXISTS ( SELECT TOP 1 1 from Employee1 )
BEGIN
	SET @HTML = 
	'<br></br>'+
	'<table border="1" width="100%" cellpadding="2" cellspacing="1">' +
	'<tr bgcolor="#98AFC7" style="font-family:Tahoma;font-weight:bold;font-size:10px;">
		<td>Id</td><td>Emp</td>
	</tr>' +
	CAST(
		( 
		SELECT IIF( ROW_NUMBER() OVER (ORDER BY Id DESC) % 2 = 0 , '#E0E0E0' , '#FFFFFF' ) "@bgcolor", '',
		    td = Id, '',
			td = Emp , '' FROM
			(
				SELECT * FROM Employee1
			)E
		FOR XML PATH('tr'), TYPE
	) AS NVARCHAR(MAX)) + 
	'</table>
	 '
END

--

OUTPUT

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com