How to edit my HTML codes for the output to highlight all cells of a row based on a condition?

Question

I have a Stored Procedure in SQL Server 2012 which provides the HTML formatting of the output a T-SQL query. It works fine and is shown below:

DECLARE @Body NVARCHAR(MAX),
        @TableHead VARCHAR(1000),
        @TableTail VARCHAR(1000)

SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
    + '</style>' + '</head>' + '<body>' + 'This report is sent by the BI Server. Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 
    + '<tr> <td bgcolor=#E6E6FA><b>StayYear</b></td>'
    + '<td bgcolor=#E6E6FA><b>PropertyCode</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jan</b></td>'
    + '<td bgcolor=#E6E6FA><b>Feb</b></td>'
    + '<td bgcolor=#E6E6FA><b>Mar</b></td>'
    + '<td bgcolor=#E6E6FA><b>Apr</b></td>'
    + '<td bgcolor=#E6E6FA><b>May</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jun</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jul</b></td>'
    + '<td bgcolor=#E6E6FA><b>Aug</b></td>'
    + '<td bgcolor=#E6E6FA><b>Sep</b></td>'
    + '<td bgcolor=#E6E6FA><b>Oct</b></td>'
    + '<td bgcolor=#E6E6FA><b>Nov</b></td>'
    + '<td bgcolor=#E6E6FA><b>Dec</b></td>'
    + '<td bgcolor=#0e914b><b>Total</b></td>';

SET @Body = ( SELECT td = StayYear, '',
                        td = PropertyCode, '',
                        td = Jan, '',
                        td = Feb, '',
                        td = Mar, '',
                        td = Apr, '',
                        td = May, '',
                        td = Jun, '',
                        td = Jul, '',
                        td = Aug, '',
                        td = Sep, '',
                        td = Oct, '',
                        td = Nov, '',
                        td = Dec, '',
                        td = Total, ''
              FROM  ITB 
              ORDER BY  [PropertyCode], [StayYear] DESC

                FOR   XML RAW('tr'),
                      ELEMENTS
            ) 


SET  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

SELECT  @Body

END

Here is an extract of the HTML output of the above query:

<html>
<head>
<style>td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} 
</style>
</head>
<body>Report generated on : 22 Feb 2018 <br> 
<table cellpadding=0 cellspacing=0 border=0>
<tr> 
<td bgcolor=#E6E6FA><b>StayYear</b></td>
<td bgcolor=#E6E6FA><b>PropertyCode</b></td>
<td bgcolor=#E6E6FA><b>Jan</b>
</td><td bgcolor=#E6E6FA><b>Feb</b></td>
<td bgcolor=#E6E6FA><b>Mar</b></td>
<td bgcolor=#E6E6FA><b>Apr</b></td>
<td bgcolor=#E6E6FA><b>May</b></td>
<td bgcolor=#E6E6FA><b>Jun</b></td>
<td bgcolor=#E6E6FA><b>Jul</b></td>
<td bgcolor=#E6E6FA><b>Aug</b></td>
<td bgcolor=#E6E6FA><b>Sep</b></td>
<td bgcolor=#E6E6FA><b>Oct</b></td>
<td bgcolor=#E6E6FA><b>Nov</b></td>
<td bgcolor=#E6E6FA><b>Dec</b></td>
<td bgcolor=#0e914b><b>Total</b></td>
<tr>
<td>2018</td>
<td>CDM</td>
<td>3261</td>
<td>2938</td>
<td>3054</td>
<td>2157</td>
<td>1948</td>
<td>889</td>
<td>1772</td>
<td>1620</td>
<td>872</td>
<td>1280</td>
<td>576</td>
<td>447</td>
<td>20814</td>
</tr><tr>
<td>2017</td>
<td>CDM</td>
<td>2989</td>
<td>2622</td>
<td>2484</td>
<td>1756</td>
<td>1326</td>
<td>552</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>321</td>
<td>465</td>
<td>225</td>
<td>12740</td>
</tr><tr>
<td>2018</td>
<td>EBA</td>
<td>1629</td>
<td>1494</td>
<td>1569</td>
<td>1085</td>
<td>1236</td>
<td>701</td>
<td>355</td>
<td>427</td>
<td>737</td>
<td>460</td>
<td>792</td>
<td>358</td>
<td>10843</td>
</tr><tr>
<td>2017</td>
<td>EBA</td>
<td>1778</td>
<td>1588</td>
<td>1580</td>
<td>1311</td>
<td>793</td>
<td>293</td>
<td>368</td>
<td>312</td>
<td>409</td>
<td>397</td>
<td>211</td>
<td>97</td>
<td>9137</td>
</tr><tr>
</td>
</table>
</body>
</html>

This is how the final html output (extract) looks:

I would like to modify my HTMLcodes in the Stored Procedure so that ALL rows relating to one same Property be highlighted in a same color. That is, for Property 'CDM', all the rows will be highlighted in, say, Red and let's say Yellow for all Property called 'EBA'.

Can this be done in HTML or am I out of luck here?

You could add the propertycode as a class to all the rows, and then create css classes with background colors. — WernerW, Feb 22 '18 at 12:07
Thanks. However, I am not very conversant with HTML. Any links that could be helpful? — user3115933, Feb 22 '18 at 12:20
The idea is to add a style class to the table rows then add a style class — WernerW, Feb 22 '18 at 12:27

score 0 · Answer 1 · answered Feb 22 '18 at 13:08

Add styles to the html based on the values like this: Tryit editor

<html>
<head>
<style>
td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} 

.cdm td { 
    background-color: #0000FF;  color: white; } 

.eba td {
    background-color: #CC9999; color: white;
}


</style>
</head>
<body>Report generated on : 22 Feb 2018 <br> 
<table cellpadding=0 cellspacing=0 border=0>
<tr> 
<td bgcolor=#E6E6FA><b>StayYear</b></td>
<td bgcolor=#E6E6FA><b>PropertyCode</b></td>
<td bgcolor=#E6E6FA><b>Jan</b>
</td><td bgcolor=#E6E6FA><b>Feb</b></td>
<td bgcolor=#E6E6FA><b>Mar</b></td>
<td bgcolor=#E6E6FA><b>Apr</b></td>
<td bgcolor=#E6E6FA><b>May</b></td>
<td bgcolor=#E6E6FA><b>Jun</b></td>
<td bgcolor=#E6E6FA><b>Jul</b></td>
<td bgcolor=#E6E6FA><b>Aug</b></td>
<td bgcolor=#E6E6FA><b>Sep</b></td>
<td bgcolor=#E6E6FA><b>Oct</b></td>
<td bgcolor=#E6E6FA><b>Nov</b></td>
<td bgcolor=#E6E6FA><b>Dec</b></td>
<td bgcolor=#0e914b><b>Total</b></td>
<tr class="cdm">
<td class="eba">2018</td>
<td>CDM</td>
<td>3261</td>
<td>2938</td>
<td>3054</td>
<td >2157</td>
<td>1948</td>
<td>889</td>
<td>1772</td>
<td>1620</td>
<td>872</td>
<td>1280</td>
<td>576</td>
<td>447</td>
<td>20814</td>
</tr><tr class="cdm">
<td>2017</td>
<td>CDM</td>
<td>2989</td>
<td>2622</td>
<td>2484</td>
<td>1756</td>
<td>1326</td>
<td>552</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>321</td>
<td>465</td>
<td>225</td>
<td>12740</td>
</tr><tr  class="eba">
<td>2018</td>
<td>EBA</td>
<td>1629</td>
<td>1494</td>
<td>1569</td>
<td>1085</td>
<td>1236</td>
<td>701</td>
<td>355</td>
<td>427</td>
<td>737</td>
<td>460</td>
<td>792</td>
<td>358</td>
<td>10843</td>
</tr><tr class="eba">
<td>2017</td>
<td>EBA</td>
<td>1778</td>
<td>1588</td>
<td>1580</td>
<td>1311</td>
<td>793</td>
<td>293</td>
<td>368</td>
<td>312</td>
<td>409</td>
<td>397</td>
<td>211</td>
<td>97</td>
<td>9137</td>
</tr><tr>
</td>
</table>
</body>
</html>

Thanks. I am having trouble inserting the following codes in my SQL Query: 2018 Where do I insert them? — user3115933, Feb 23 '18 at 13:28
I am not used to XML in my queries, but i would guess you could try replacing the line FOR XML RAW('tr'), With FOR XML RAW('tr class='''+ISNULL([PropertyCode],'')+''''), Hope this helps — WernerW, Feb 23 '18 at 13:46
I have the following error message in SQL: Incorrect syntax near '+' — user3115933, Feb 23 '18 at 15:09
Maybe this can help you a bit more: https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml — WernerW, Feb 26 '18 at 09:16

How to edit my HTML codes for the output to highlight all cells of a row based on a condition?

1 Answers1