使用FOR XML PATH在SQL Server中格式化颜色 [英] Formating color in SQL Server using FOR XML PATH
问题描述
我已经使用这种方法,但现在我必须根据值使用一些颜色。所以,我在表格中有以下信息:
材料| Q1 | Q2
--------- + ---- + -----
M1 | 10 | 5
M2 | 3 | 10
M3 | 15 | 15
当Q1低于Q2时,我想用红色表示蓝色和黄色何时是相同的价值。
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 = tablaPrincipalborder = 0>
< tr style =background:#a7bfde; font-weight:bold;>
< td> q1< / td>
< td> q2< / td>
< td>比较< / 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
我尝试将td与另一个标记tdx并替换为:
SET @html = REPLACE(@html,'','')
但是,可以更改动态取决于值的别名?
谢谢!
使用中提到的函数我的评论你会这样做:
这是函数
CREATE FUNCTION dbo.CreateHTMLTable
(
@SelectForXmlPathRowElementsXsinil XML
,@ tblClass VARCHAR(100)--NULL忽略此类
,@ 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]
返回
< tr>
{
for $ th in $ first / *
return< ($ th / @ caption)))然后xs:string($ th / @ caption)else local-name($ th)}< / th> (b
)
')AS thead
,@ tbClass AS [tbody / @ class]
,@ SelectForXmlPathRowElementsXsinil.query(
N' for $ tr in / row
返回
< tr> {$ tr / @ class}
{
for $ td in $ tr / *
return
if(空($ td / @ link))
然后< 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
- 您的测试表
INSERT INTO #tempo(q1,q2,name)
VALUES(10,5,'low'),(10,10,'same'),(10,20,'高');
GO
- 内联CSS,便于格式化
DECLARE @inlineCSS XML =
N'< style type =text / cssmedia =screen,print >
.low
{
颜色:黑色;
背景颜色:红色;
}
.same
{
颜色:黑色;
background-color:yellow;
}
.high
{
颜色:白色;
background-color:blue;
}
表格,th
{
border:1px纯黑色;
}
< / style>';
- 这是实际的查询
SELECT @inlineCSS
,dbo.CreateHTMLTable
(
(
SELECT
CASN when ISNULL (q1,0)> ISNULL(q2,0)THEN'低'
ELSE CASE当ISNULL(q2,0)> ISNULL(q1,0)THEN'高'
ELSE'相同'
END
END AS [@class]
,名称AS材料
,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');
- 提示:使用classnames而不是三个,NULL,NULL, NULL
允许将常规CSS类放置到< table>
,< thead>
和< tbody>
。
- 清理
GO
DROP TABLE #tempo
GO
DROP FUNCTION dbo.CreateHTMLTable;
这是结果(点击运行查看输出结果)
< html> <身体GT; < style type =text / cssmedia =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> <表> < THEAD> < TR> <的第i;材料< /第> <的第i; Q1< /第> <的第i; Q2< /第> < / TR> < / THEAD> < TBODY> < tr class =low> < TD>低速< / TD> < TD→10< / TD> < TD> 5℃; / TD> < / TR> < tr class =same> < TD>相同< / TD> < TD→10< / TD> < TD→10< / TD> < / TR> < tr class =high> < TD>高< / TD> < TD→10< / TD> < TD> 20℃; / TD> < / TR> < / tbody的> < /表> < / body>< / html>
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!
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>
这篇关于使用FOR XML PATH在SQL Server中格式化颜色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!