SQL query mail address multiple lines merge one line, then send the message

--创建测试表use FlygoIF OBJECT_ID(‘test‘) IS NOT NULL DROP TABLE testCREATE TABLE test( Id int IDENTITY(1,1) NOT NULL, Name varchar(50) NULL, Judge char(1) NULL, Mail varchar(50) NULL)GO--插入测试数据INSERT INTO test ( Name ,Judge ,Mail) SELECT ‘张三‘,‘Y‘,‘[email protected]‘ UNION ALL SELECT ‘李四‘,‘Y‘,‘[email protected]‘ UNION ALL SELECT ‘王五‘,‘N‘,‘[email protected]‘ GOSELECT * FROM test--查询多行合并DECLARE @Maliaddress varchar(8000)set @Maliaddress = ‘‘‘‘ + STUFF((SELECT ‘;‘ + Mail FROM test WHERE Judge in (‘Y‘) FOR xml path(‘‘)),1,1,‘‘) + ‘‘‘‘ select @Maliaddress as ‘MailAddress‘ exec msdb.dbo.sp_send_dbmail@profile_name = ‘Fly_Huang‘, @recipients = @Maliaddress, @subject = ‘Test电子邮件的主题‘, @body = ‘Test电子邮件的正文‘, @body_format = ‘HTML‘

SQL query mail address multiple lines merge one line, then send the message