How can I improve this Mailing Address SQL Server SELECT Statement?

How can I format a Mailing Address so that I always push all non-null rows to the top? That is, I want to convert an address from the structure below to a mailing address.

Here is the structure:

[Line1] [varchar](50) NULL,
[Line2] [varchar](50) NULL,
[Line3] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar] (2) NULL,
[PostalCode] [varchar](50) NULL,

Here is some sample data:

Line1=
Line2=123 Some Address
Line3=
City=Royal Oak
State=MI
ZIP=45673-2312

Here is what the result should look like (4 distinct or separate fields should be returned):

MailAddress1=123 Some Address
MailAddress2=ROYAL OAK MI 45673-2312
MailAddress3=
MailAddress4=

I am using SQL Server 2005.

Someone wrote this logic in our company and it just seemed to complex (Note: this is not the whole SELECT statement):

,CASE 
  WHEN eai.Line1 IS NULL OR eai.Line1 = '' THEN 
    CASE 
      WHEN eai.Line2 IS NULL OR eai.Line2 = '' THEN 
        CASE 
          WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
          ELSE eai.Line3
          END
      ELSE eai.Line2
      END
  ELSE eai.Line1
  END
,CASE 
  WHEN eai.Line1 IS NULL OR eai.Line1 = '' THEN
    CASE 
      WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
      ELSE eai.Line3
      END
  ELSE 
    CASE 
      WHEN eai.Line2 IS NULL OR eai.Line2 = '' THEN 
        CASE 
          WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
          ELSE eai.Line3
          END
      ELSE eai.Line2
      END
  END
,CASE
  WHEN eai.Line1 IS NULL OR eai.Line1 = '' THEN
    CASE 
      WHEN eai.Line2 IS NULL OR eai.Line2 = '' THEN NULL
      ELSE
        CASE 
          WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN NULL
          ELSE ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
          END
      END 
  ELSE
    CASE 
      WHEN eai.Line2 IS NULL OR eai.Line2 = '' THEN 
        CASE 
          WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN NULL
          ELSE ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
          END
      ELSE 
          CASE 
            WHEN eai.Line3 IS NULL OR eai.Line3 = '' THEN ISNULL(LTRIM(RTRIM(eai.City)),'') + ' ' + ISNULL(LTRIM(RTRIM(eai.RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(eai.PostalCode)),'')
            ELSE eai.Line3
            END
      END
  END
,CASE WHEN eai.Line2 IS NOT NULL AND eai.Line2 <> '' AND eai.Line3 IS NOT NULL AND eai.Line3 <> '' THEN eai.City + ' ' + eai.RegionCode + '  ' + eai.PostalCode ELSE NULL END

From stackoverflow

• SELECT 
      LTRIM(RTRIM(LINE1)) + LTRIM(RTRIM(LINE2)) + LTRIM(RTRIM(LINE3)) AS MailAddress1,
      LTRIM(RTRIM(CITY)) + ' ' + LTRIM(RTRIM(STATE)) + ' ' + LTRIM(RTRIM(POSTALCODE)) AS MailAddress2
  FROM MyTable
  

  Julian Aubourg : Not exactly what's being asked for ;)

  Gerhard Weiss : That is correct. I would like to have four separate output fields.

  Mark Brittingham : Right - this one is not correct.

• Here's a three-minutes-invested solution:

  DECLARE @address TABLE (
      [Line1] [varchar](50) NULL,
      [Line2] [varchar](50) NULL,
      [Line3] [varchar](50) NULL,
      [City] [varchar](50) NULL,
      [State] [varchar] (2) NULL,
      [PostalCode] [varchar](50) NULL
  )
  
  INSERT INTO @address (
      [Line1],
      [Line2],
      [Line3],
      [City],
      [State],
      [PostalCode]
  )
  VALUES (
      NULL,
      '123 Some Address',
      NULL,
      'Royal Oak',
      'MI',
      '45673-2312'
  )
  
  SELECT * FROM @address
  
  SELECT
        ISNULL(Line1 + CHAR(13), '')
      + ISNULL(Line2 + CHAR(13), '')
      + ISNULL(Line3 + CHAR(13), '')
      + ISNULL(City    + ' ',    '')
      + ISNULL([State] + ' ',    '')
      + ISNULL(PostalCode,       '')
  FROM @address
  

  Result:

  123 Some Address
  Royal Oak MI 45673-2312
  

  Fiddle with the control characters until you get the result you need.

  DJ : I think the OP wants four separate output fields.

  Gerhard Weiss : That is correct. I would like to have four separate output fields.

  Rob Garrison : Do you need this to be set-based, or are you calling a function or stored procedure per-address?

  Gerhard Weiss : set-based but they are calling a table-value function to get the active address (we keep a history of all address for one person.)

  Gerhard Weiss : I do not know why they are using 4 output fields. This is being used in a Aspose Word Merge and I bet one output field would have probably worked with the code Rob supplied. I will have to ask the Aspose developer.

  Rob Garrison : Using four output fields will make the SQL much more complex. If you can handle it with the simple query above (plus RTRIM/LTRIM), it will be much simpler to maintain.

• The way to do this is with an UNPIVOT. Here is the solution:

  With AddrTable as (
  Select AddrFld, MailAddr From (
  Select Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
         Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
         Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
         Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
  From TableName Where UniqueID=@UniqueID) p
  Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
  Select Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
  MailAddr From AddrTable 
  Order By RN
  

  Here's the output:

  Address1
  Westby WI  55555
  -empty line-
  -empty line-
  

  Note that I had to use "Varchar(102)" as the field length (unpivot requires that all fields be the same) because your City/Region/Postal can have up to 102 chars in total. Also, note that "@UniqueID" is the identifier for the record whose address you need. This returns four and always four rows containing the data you need for your address.

  UPDATE: If you need to return this as a set of four columns rather than four rows, then just plop it into a view and then query the view with a Pivot. I've included the view here for completeness as I had to change the above just a bit when creating the view so the uniqueID field was included and no sort was done (the sort is now done in the query):

  Create View AddressRows AS
   With AddrTable as (
   Select UniqueID, AddrFld, MailAddr From (
   Select UniqueID, 
         Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
         Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
         Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
         Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
   From TableName Where UniqueID=@UniqueID) p
   Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
   Select UniqueID, 
         Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
         MailAddr From AddrTable
  

  And then, when you want to pull your matching "row" out, Pivot it back using this SQL (notice that I am querying again using UniqueID):

  Select [Addr1], [Addr2], [Addr3], [Addr4] From (
  Select Top 4 'Addr' + Cast(Row_Number() over (Order by RN) as Varchar(12)) as AddrCol,  -- "Top 4" needed so we can sneak the "Order By" in 
  MailAddr 
  From AddressRows Where UniqueID=@UniqueID
  ) p PIVOT (Max([MailAddr]) for AddrCol in ([Addr1], [Addr2], [Addr3], [Addr4])
  ) as pvt
  

  This returns:

  Addr1            Addr2                Addr3           Addr4
  --------------   ------------------   -------------   ------------------ 
  Address1         Westby WI  54667
  

  Mark Brittingham : This will give you as many records as are found...does it need to be four? There is a way to do that as well.

  Mark Brittingham : Also, do you need this for a specific record? E.g. ClientID=1234

  Gerhard Weiss : Yes to both questions Mark.

  Mark Brittingham : Ok, example two chooses the records for just the given ClientID (unique identifier). Let me cook up a version with four records. I take it that you just want the records returned (not the "MailAddressX="). Is this right or do you need the descriptor?

  Gerhard Weiss : No desciptor is needed. Just the address information.

  Mark Brittingham : Dayam - just did the descriptor. Oh well, that makes it easier to return four lines...

  Mark Brittingham : Although the first solution did let me use the cool new "Row_Number() over (Order by FieldName)" construct.

  Mark Brittingham : Actually, once I realized that the four fields gave me the four output rows, the Row_Number() stays. I changed my fields to your fields so the only thing you should have to do is to change the table name. Note, too, that this does return the row number as well as the address.

  Portman : I wish I had more than one vote to give. Well played, sir.

  Mark Brittingham : Thank you Portman

  Mark Brittingham : Gerhard - I updated your query a bit and, in so doing, realized that you needed columns rather than rows. Easy enough, I updated the answer as well to provide columns.

  Gerhard Weiss : @Mark Brittingham, I am going to give you the SQL Stud badge.

  Mark Brittingham : Lol - I do tend to do best with SQL questions here on SO.