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
Peter 24 0.7
Peter 29 1.4
Peter 34 2.6

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
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.Age
                               For xml Path('tr'), elements) As varchar(max));
 --==== Send Notification if we have results
     If @xmlResults Is Not Null 

 --==== 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;


The xmlResults from this will look like this:

  <td />

I have removed the extra blank row at the end - you can adjust that if needed.