多行列表循环合并数据发送单封邮件的技术实现求助

嘿，我来帮你搞定这个头疼的问题！你现在的情况是，员工的银行变更记录被拆成了5行存在AuditCondensed表里，每行只对应一个字段的变更，导致你现在只能发5封缺斤短两的邮件对吧？别慌，我们先把这5行合并成一条完整记录，再发邮件就搞定了！

问题核心分析 #

你的表结构里，每个员工的银行变更信息分散在5行中：前5个字段（AuditFieldID、CompanyRuleID、CompanyRule、VipUserName、EffectiveDate）完全相同，FieldName字段区分不同的变更类型（比如账户持有人、账号等），且每行只填充对应类型的新旧值字段，其余为空。直接遍历每行发邮件自然会得到5封不完整的邮件，关键是先把同一员工的5条记录合并成一条包含所有变更信息的完整记录。

解决方案：行转列合并+邮件发送 #

我们用SQL的GROUP BY配合CASE语句做行转列，把同一员工的分散记录合并成一行，再基于这条完整记录生成邮件内容并发送。

完整SQL代码 #

-- 第一步：合并同一员工的所有变更记录为单行
WITH MergedAuditData AS (
    SELECT
        [CompanyRuleID],
        [CompanyRule],
        [VipUserName],
        [EffectiveDate],
        [SourceCode],
        [Action],
        -- 匹配FieldName提取对应字段的新旧值，MAX过滤空值
        MAX(CASE WHEN FieldName = 'Account Holder Name' THEN AccountHolderOldValue END) AS AccountHolderOldValue,
        MAX(CASE WHEN FieldName = 'Account Holder Name' THEN AccountHolderNewValue END) AS AccountHolderNewValue,
        MAX(CASE WHEN FieldName = 'Account Number' THEN AccountNoOldValue END) AS AccountNoOldValue,
        MAX(CASE WHEN FieldName = 'Account Number' THEN AccountNoNewValue END) AS AccountNoNewValue,
        MAX(CASE WHEN FieldName = 'Account Type' THEN AccountTypeOldValue END) AS AccountTypeOldValue,
        MAX(CASE WHEN FieldName = 'Account Type' THEN AccountTypeNewValue END) AS AccountTypeNewValue,
        MAX(CASE WHEN FieldName = 'Bank' THEN BankOldValue END) AS BankOldValue,
        MAX(CASE WHEN FieldName = 'Bank' THEN BankNewValue END) AS BankNewValue,
        MAX(CASE WHEN FieldName = 'Bank Branch' THEN BranchOldValue END) AS BranchOldValue,
        MAX(CASE WHEN FieldName = 'Bank Branch' THEN BranchNewValue END) AS BranchNewValue
    FROM [SageStaging].[MASSMART].[AuditCondensed]
    -- 按同一员工的标识字段分组，确保5条记录合并为一行
    GROUP BY [CompanyRuleID], [CompanyRule], [VipUserName], [EffectiveDate], [SourceCode], [Action]
)
-- 第二步：遍历合并后的完整记录，发送单封邮件
DECLARE @MailCursor CURSOR;
DECLARE @MailSubject NVARCHAR(255), @MailBody NVARCHAR(MAX);

-- 初始化游标，读取每条合并后的员工记录
SET @MailCursor = CURSOR FOR
SELECT 
    'Banking Details Change Notification for Employee ' + SourceCode,
    '<html> 
<head> 
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type"> 
<title></title> 
</head> 
<body> 
<br> The following bank details have been changed: <br> <br> 
Date Changed: ' + EffectiveDate + '<br>' + 
' Company: ' + CompanyRule + '<br>' + 
' Username: ' + VipUserName + '<br>' + 
' Employee Details: ' + SourceCode + '<br>' + 
' Action: ' + Action + '<br>' + 
' Account Holder: Old Value: ' + ISNULL(AccountHolderOldValue, 'N/A') + ' New Value: ' + ISNULL(AccountHolderNewValue, 'N/A') + '<br>' + 
' Account Number: Old Value: ' + ISNULL(AccountNoOldValue, 'N/A') + ' New Value: ' + ISNULL(AccountNoNewValue, 'N/A') + '<br>' + 
' Account Type: Old Value: ' + ISNULL(AccountTypeOldValue, 'N/A') + ' New Value: ' + ISNULL(AccountTypeNewValue, 'N/A') + '<br>' + 
' Bank: Old Value: ' + ISNULL(BankOldValue, 'N/A') + ' New Value: ' + ISNULL(BankNewValue, 'N/A') + '<br>' + 
' Bank Branch: Old Value: ' + ISNULL(BranchOldValue, 'N/A') + ' New Value: ' + ISNULL(BranchNewValue, 'N/A') + '<br>' + 
'<br> <br> <b> Please do not respond to this email. If you have any questions regarding this email, please contact your payroll administrator <br> <br> <br> </body>'
FROM MergedAuditData;

-- 开启游标并发送邮件
OPEN @MailCursor;
FETCH NEXT FROM @MailCursor INTO @MailSubject, @MailBody;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Your-Mail-Profile', -- 替换成你的SQL邮件配置名称
        @recipients = 'payroll-team@yourcompany.com', -- 替换成实际收件人邮箱
        @subject = @MailSubject,
        @body = @MailBody,
        @body_format = 'HTML';

    FETCH NEXT FROM @MailCursor INTO @MailSubject, @MailBody;
END;

-- 清理游标
CLOSE @MailCursor;
DEALLOCATE @MailCursor;

关键细节说明 #

1. 行转列逻辑：CASE语句匹配每个FieldName，提取对应的新旧值；MAX函数自动过滤空值（因为每个FieldName只有一行有有效数据，其余行都是NULL），最终把5行数据合并为一行完整记录。
2. 空值处理：用ISNULL函数把空的新旧值替换为N/A，避免邮件中出现空白内容，提升可读性。
3. 批量邮件发送：用游标遍历合并后的每条记录，给每个员工发送仅包含其完整变更信息的单封邮件，不再重复发送。

内容的提问来源于stack exchange，提问作者Jeremy Reynolds