To do what you are asking - you need to add a 'blank' row into the table definition. To get a blank line to sort after each group - you need to provide a value that sorts after that group but before the next group.
Here is an email template that I modified to satisfy your requirements:
Set Nocount On;
--==== Sample data
Declare @testTable Table (
Names varchar(10)
, Age int
, Rate decimal(4,2)
);
Insert Into @testTable (Names, Age, Rate)
Values ('John', 20, 0.5)
, ('John', 24, 0.7)
, ('John', 23, 0.9)
, ('Peter', 24, 0.7)
, ('Peter', 29, 1.4);
--==== End sample data
--==== Email template
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'recipient addresses'
, @cc_recipients varchar(max) = 'cc addresses';
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
From (
Values ('Name', 'Age', 'Rate')
) As html(hdr1, hdr2, hdr3)
For xml Path('tr'), elements) As varchar(max));
--==== Get the results as an XML table
Set @xmlResults = cast((Select iif(t.Names Like '%zz', '', t.Names) As td, ''
, t.Age As td, ''
, t.Rate As td, ''
From (Select tt.Names
, tt.Age
, tt.Rate
From @testTable tt
Union All
Select Distinct
concat(tt.Names, 'zz') As Names
, Null As Age
, Null As Rate
From @testTable tt
) As t
Order By
t.Names
, t.Age
For xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Remove the 'last' blank line
Set @xmlResults = substring(@xmlResults, 1, len(@xmlResults) - 18);
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:left; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body><p> ***** body of email here **** </p> ';
--==== Setup the table with the list of new document types
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{database mail profile}'
, @from_address = '{from address}'
, @reply_to = '{reply-to address}'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @subject = '{subject of email}'
, @body_format = 'HTML'
, @body = @body;
End
Go
The xmlResults from this will look like this:
<tr>
<td>John</td>
<td>20</td>
<td>0.50</td>
</tr>
<tr>
<td>John</td>
<td>23</td>
<td>0.90</td>
</tr>
<tr>
<td>John</td>
<td>24</td>
<td>0.70</td>
</tr>
<tr>
<td />
</tr>
<tr>
<td>Peter</td>
<td>24</td>
<td>0.70</td>
</tr>
<tr>
<td>Peter</td>
<td>29</td>
<td>1.40</td>
</tr>
I have removed the extra blank row at the end - you can adjust that if needed.