使用：Microsoft SQL Server 2014
我有一个表格，我将其作为报告发送电子邮件，以表明哪些州的销售计数(或报价请求或未订阅电子邮件等(发生了重大变化：
-- Sample Table
CREATE TABLE mytable(
State VARCHAR(2) NOT NULL PRIMARY KEY
,Count1 BIT
,Count2 BIT
,Count3 BIT
,Count4 BIT
,Count5 BIT
,Count6 BIT
,Count7 BIT
,Count8 BIT NOT NULL
);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('KS',NULL,1,NULL,1,1,1,1,1,1);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('KY',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('LA',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,1);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('MA',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,1);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('MD',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,1);
INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('ME',NULL,1,NULL,NULL,1,NULL,NULL,NULL,1)
表格看起来像这样：
"State"_______"Count1"________"Count2"_____"Count3"
AK 1
AL
CA 1
CO 1
CT 1
DE 1 1
FL 1
GA 1
(对于许多计数…，对于每个州/联邦区/保护国等(
我用这种方式发电子邮件：
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
--Set Data
SET @xml = CAST(( SELECT DISTINCT [State] AS 'td',''
,[Count1] AS 'td',''
,[Count2] AS 'td',''
,[Count3] AS 'td',''
FROM mytable
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
--Set columns
SET @body ='
<html><body><H3>Altered State Counts</H3>
<table border = 1>
<tr>
<th>[State] </th>
<th>[Count1] </th>
<th>[Count2] </th>
<th>[Count3] </th>'
--Set table format
SET @body = @body+ @xml +'</table></body></html>'
--Send Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLEMAIL',
@body = @body,
@body_format ='HTML',
@recipients = 'me@initech.com',
@subject = 'TPS Reports' ;
这很有效，并生成一封好的电子邮件，看看你是否使用了最大化的浏览器，并愿意来回滚动。。。我真正想要的是：
.................................
TPS报告
"Count1:" AK, CA
"Count2:" CT
"Count3:" AK, AL, CA, CO, CT, DE, FL, GA
当这些计数列下面有一个1时。
如果您将其放入上述代码中，它应该提供您为电子邮件寻求的格式。
--Set Data
SELECT Counts, [State]
INTO #tempCounts
FROM (
SELECT [State]
,[Count1]
,[Count2]
,[Count3]
FROM mytable ) tt
UNPIVOT
(States FOR Counts IN ([Count1], [Count2], [Count3])) u
SET @xml = CAST((SELECT DISTINCT Counts AS td,'', Stuff((SELECT DISTINCT ',' + [State] FROM #tempCounts t2 WHERE t2.Counts = t1.Counts FOR XML PATH('') ), 1,1, '') AS td
FROM #tempCounts t1
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
--Set Columns
如果你想添加更多的列(尤其是如果你的列超过9(，你必须使用动态SQL来解决这个问题。提示，您可以像对TR元素所做的那样，使用FOR XML PATH列出所有列。
此外，不要忘记在过程结束时删除#tempCounts表。