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

Question

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(R…

Accepted Answer

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                                                 
```

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

Solution

Problem