SQLTeam.com | Weblogs | Forums

Insert a blank row in results, How?

I have a stored proc which extracts the results into an email. I need to to the following:

  1. Sort results by first column and then by 2nd column. I guess that is just a simple ORDER BY 1,2
  2. I also need to insert a blank row after each group based on the first column. E.g. There will be 5 records with the same value in column 1 with different values in column 2. I need to put a blank row after all of the records where column 1 is the same, then do the same after the second group (column 1):

Something like this:

Names Age Rate
John 20 0.5
John 24 0.7
John 23 0.9
BLANK ROW
Peter 24 0.7
Peter 29 1.4
Peter 34 2.6
BLANK ROW

The query at the moment looks like this:

SET @eBODY = '

'
'SELECT  @eBODY = @eBODY + '<tr><td>' + Names + '</td><td>' + Age + '</td><td>' + Rate + '</td><td>'

from Ruletests as rt
inner join Rules as r
on r.id = rt.names
and r.iCategory = 0
and sRuleSet <> 'NOTIFY'

Could you please help me with the Blank Row. I've tried the BREAK command but this seems to be more Oracle specific.

Name Age Rate

YOu can try adding a NewLine to it. Something like this

create table t (Name varchar(10), age int, rate decimal(4,2))

insert into t
values
('John',20,0.5)
,('John',24,0.7)
,('John',23,0.9)
,('Peter',24,0.7)
,('Peter',29,1.4)
--,('Peter',34,2.6)
select Case when Row = 1 then char(13) + Name else Name end, Age, Rate
from (
select *,
rOW_NUMBER() OVER (partition by name order by age) Row
from t) x

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.