title: How can I improve this Mailing Address SQL Server SELECT Statement?
body: <p>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.</p>
  
  <p>Here is the structure:</p>
  
  <pre><code>[Line1] [varchar](50) NULL,
  [Line2] [varchar](50) NULL,
  [Line3] [varchar](50) NULL,
  [City] [varchar](50) NULL,
  [State] [varchar] (2) NULL,
  [PostalCode] [varchar](50) NULL,
  </code></pre>
  
  <p>Here is some sample data:</p>
  
  <pre><code>Line1=
  Line2=123 Some Address
  Line3=
  City=Royal Oak
  State=MI
  ZIP=45673-2312
  </code></pre>
  
  <p>Here is what the result should look like <strong>(4 distinct or separate fields should be returned)</strong>: </p>
  
  <pre><code>MailAddress1=123 Some Address
  MailAddress2=ROYAL OAK MI 45673-2312
  MailAddress3=
  MailAddress4=
  </code></pre>
  
  <p>I am using SQL Server 2005.</p>
  
  <p>Someone wrote this logic in our company and it just seemed to complex (Note: this is not the whole SELECT statement):</p>
  
  <pre><code>,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 &lt;&gt; '' AND eai.Line3 IS NOT NULL AND eai.Line3 &lt;&gt; '' THEN eai.City + ' ' + eai.RegionCode + '  ' + eai.PostalCode ELSE NULL END
  </code></pre>