Reputation: 29

Formating color in SQL Server using FOR XML PATH

I already used this method, but now I have to use some colors depending on the values. So, I have the following information in a table:

Material | Q1 | Q2
---------+----+-----
M1       | 10 | 5
M2       | 3  | 10
M3       | 15 | 15

When the Q1 is lower than Q2 I want to color red, when is high in blue and yellow when is the same value.

CREATE TABLE #tempo
(
    q1 INT, q2 INT, name VARCHAR(10)
)

INSERT INTO #tempo (q1, q2, name)
VALUES (10, 5, 'low'), (10, 10, 'same'), (10, 20, 'high')

--SELECT * FROM #tempo

DECLARE @html varchar(MAX)
SET @html = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'+
                    (
                        SELECT 
                            isnull(q1,0) AS td
                            ,' ' , isnull(q2,0) AS td
                            ,' ' , name AS td

                    FROM #tempo
                    FOR XML PATH('tr')
                    )
                    +'</TABLE>'        
SELECT @html
DROP TABLE #tempo

I tried to use td with another tag tdx and replace like this: SET @html = REPLACE(@html, '', '')

but, it is possible to change the alias dynamically depending on the value?

Thanks!

Upvotes: 1

Answers (3)

Gottfried Lesigang

Reputation: 67311

With the function mentioned in my comments you'd go like this:

This is the function

CREATE FUNCTION dbo.CreateHTMLTable
(
    @SelectForXmlPathRowElementsXsinil XML
   ,@tblClass VARCHAR(100) --NULL to omit this class
   ,@thClass VARCHAR(100)  --same
   ,@tbClass VARCHAR(100)  --same
)
RETURNS XML
AS
BEGIN

RETURN 
(
    SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
              N'let $first:=/row[1]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{if(not(empty($th/@caption))) then xs:string($th/@caption) else local-name($th)}</th>
                }
                </tr>') AS thead
    ,@tbClass AS [tbody/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
               N'for $tr in /row
                 return 
                 <tr>{$tr/@class}
                 {
                 for $td in $tr/*
                 return
                 if(empty($td/@link)) 
                 then <td>{$td/@class}{string($td)}</td>
                 else <td>{$td/@class}<a href="{$td/@link}">{string($td)}</a></td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
) 
END
GO

--Your test table

CREATE TABLE #tempo
(
    q1 INT, q2 INT, name VARCHAR(10)
);

INSERT INTO #tempo (q1, q2, name)
VALUES (10, 5, 'low'), (10, 10, 'same'), (10, 20, 'high');
GO

--Inline CSS for easy formatting

DECLARE @inlineCSS XML=
N'<style type="text/css" media="screen,print">
.low
{
    color: black;
    background-color: red;
}
.same
{
    color: black;
    background-color: yellow;
}
.high
{
    color: white;
    background-color: blue;
}
table,th
{
    border: 1px solid black;
}
</style>';

--This is the actual query

SELECT @inlineCSS 
      ,dbo.CreateHTMLTable
        (
             (
             SELECT 
               CASE WHEN ISNULL(q1,0)>ISNULL(q2,0) THEN 'low' 
                    ELSE CASE WHEN ISNULL(q2,0)>ISNULL(q1,0) THEN 'high' 
                         ELSE 'same' 
                         END 
                    END AS [@class] 
              ,name AS Material
              ,ISNULL(q1,0) AS [Q1]
              ,ISNULL(q2,0) AS [Q2]
             FROM #tempo 
             FOR XML PATH('row'),ELEMENTS XSINIL),NULL,NULL,NULL
        )
FOR XML PATH('body'),ROOT('html');

--Hint: Using classnames instead of the three ,NULL,NULL,NULL allows to place general CSS classes to the <table>, the <thead> and the <tbody>.

--Clean-Up

GO
DROP TABLE #tempo
GO
DROP FUNCTION dbo.CreateHTMLTable;

This is the result (click "Run" to see the output)

<html>
  <body>
    <style type="text/css" media="screen,print">
.low
{
    color: black;
    background-color: red;
}
.same
{
    color: black;
    background-color: yellow;
}
.high
{
    color: white;
    background-color: blue;
}
table,th
{
	border: 1px solid black;
}
</style>
    <table>
      <thead>
        <tr>
          <th>Material</th>
          <th>Q1</th>
          <th>Q2</th>
        </tr>
      </thead>
      <tbody>
        <tr class="low">
          <td>low</td>
          <td>10</td>
          <td>5</td>
        </tr>
        <tr class="same">
          <td>same</td>
          <td>10</td>
          <td>10</td>
        </tr>
        <tr class="high">
          <td>high</td>
          <td>10</td>
          <td>20</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

Upvotes: 3

John Cappelletti

Reputation: 81960

Here is another option. Not clear if you wanted just the one cell highlighted

EDIT - Updated for 3 colors

Example

DECLARE @html varchar(MAX)
SET @html = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'+
                    (
                    SELECT [td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = isnull(q1,0)
                          ,null
                          ,[td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = isnull(q2,0) 
                          ,null
                          ,[td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = name 
                     FROM #tempo
                    FOR XML PATH('tr')
                    )
                    +'</TABLE>'        
SELECT @html

Returns

Upvotes: 7

Sean Lange

Reputation: 33581

How about something like this.

CREATE TABLE #tempo(
q1 INT, q2 INT, name VARCHAR(10)
)

INSERT INTO #tempo(q1,q2,name)VALUES(10,5,'low')
INSERT INTO #tempo(q1,q2,name)VALUES(10,10,'same')
INSERT INTO #tempo(q1,q2,name)VALUES(10,20,'high')

DECLARE @html varchar(MAX) = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'

SELECT @html = @html + '<tr style="background:' + case when q1 < q2 then 'red' when q1 > q2 then 'blue' else 'yellow' end + ';"><td>' + convert(varchar(10), q1) + '</td><td>' + convert(varchar(10), q2) + '</td></tr>'
FROM #tempo

select @html = @html + '</table>'

select @html
DROP TABLE #tempo

Upvotes: 2

Formating color in SQL Server using FOR XML PATH

Answers (3)

Related Questions