How can I improve this Mailing Address SQL Server

问题:

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

回答1:

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                                                 

回答2:

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.

回答3:

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