T-sql – Removal of blank lines

concatssrst-sql

I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows:

SELECT DISTINCT 
                         AddressLink.intAddressID, Addresses.txtLabelSalutation, Addresses.txtAddress1, Addresses.txtAddress2, Addresses.txtAddress3, Addresses.txtTown, 
                         Addresses.txtCounty, Addresses.txtCountry, Addresses.txtPostcode, Addresses.txtRelationType, Pupils.intNCYear, Pupils.txtSchoolID, Pupils.txtForename, 
                         Pupils.txtSurname
FROM            TblPupilManagementAddresses AS Addresses INNER JOIN
                         TblPupilManagementAddressLink AS AddressLink ON Addresses.TblPupilManagementAddressesID = AddressLink.intAddressID LEFT OUTER JOIN
                         TblPupilManagementPupils AS Pupils ON AddressLink.txtSchoolID = Pupils.txtSchoolID
WHERE        (Pupils.intSystemStatus = 1) AND (Addresses.txtAddress1 <> '') AND (Addresses.txtRelationType <> 'OLD Billing') AND (Addresses.intJustContact <> 1)

And within the report itself I am using the following expression to produce the addresses:

=Fields!txtLabelSalutation.Value & vbCRLF & 
Fields!txtAddress1.Value & vbCRLF &
IIF(
       Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value, nothing) & " " &
       IIF (
                     Fields!txtAddress3.Value<> "", Fields!txtAddress3.Value, nothing) & vbCRLF & Fields!txtTown.Value
       & vbCRLF &
       IIF(
              Fields!txtCounty.Value <> "", Fields!txtCounty.Value, nothing) & vbCRLF &
              IIF(
                     Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value, nothing) & vbCRLF & Fields!txtPostcode.Value

My issue is that this expression is producing blank lines for fields that have no data, so I am getting a format like:

Mr & Mrs Smith
22 Somewhere Street


Somewhere Town

SS48 9TQ

How can I concatenate rows together if there are blanks so the address appears in one clean block? I am sure I need to amend my expression, but I am not sure how. I need it to look like:

Mr & Mrs Smith
22 Somewhere Street
Somewhere Town
SS48 9TQ

I have already tried amending the SQL statement using a CAST, however this then removes the DISTINCT clause and this part is vital. I also tried by grouping with the cast but that also failed.

Best Answer

With a bit of help I have solved this, but thought I would post the answer in case it helps someone else.

I have changed the expression to read:

=Fields!txtLabelSalutation.Value & vbCRLF & Fields!txtAddress1.Value & vbCRLF &
    IIF(
            Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value & vbCRLF, nothing) &
    IIF (
            Fields!txtAddress3.Value <> "", Fields!txtAddress3.Value & vbCRLF, nothing) &
    IIF(        
            Fields!txtTown.Value <> "", Fields!txtTown.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCounty.Value <> "", Fields!txtCounty.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtPostcode.Value <> "", Fields!txtPostcode.Value & vbCRLF, nothing)

My problem was where I was including the line break within the statement.