ansaurus

Question

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

Answer 1

A:

shahkalpesh 2009-03-16 20:09:37

Not exactly what's being asked for ;)

Julian Aubourg 2009-03-16 20:11:11

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

Gerhard Weiss 2009-03-16 20:20:52

Right - this one is not correct.

Mark Brittingham 2009-03-16 20:35:17

Answer 2

+1 A:

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.

Rob Garrison 2009-03-16 20:11:23

I think the OP wants four separate output fields.

DJ 2009-03-16 20:16:37

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

Gerhard Weiss 2009-03-16 20:20:17

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

Rob Garrison 2009-03-16 20:31:07

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 2009-03-16 20:38:03

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.

Gerhard Weiss 2009-03-16 20:44:49

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.

Rob Garrison 2009-03-16 21:39:52

Answer 3

+7 A:

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 2009-03-16 20:34:42

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 2009-03-16 20:36:10

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

Mark Brittingham 2009-03-16 20:38:13

Yes to both questions Mark.

Gerhard Weiss 2009-03-16 20:47:55

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?

Mark Brittingham 2009-03-16 20:49:24

No desciptor is needed. Just the address information.

Gerhard Weiss 2009-03-16 20:58:34

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

Mark Brittingham 2009-03-16 21:08:56

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

Mark Brittingham 2009-03-16 21:09:56

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.

Mark Brittingham 2009-03-16 21:36:55

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

Portman 2009-03-16 22:06:12

Thank you Portman

Mark Brittingham 2009-03-16 22:21:09

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.

Mark Brittingham 2009-03-16 22:41:02

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

Gerhard Weiss 2009-03-17 21:01:39

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

Mark Brittingham 2009-03-17 22:24:39

ansaurus

tags:

views:

answers:

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

related questions