Address Formatting

[NiceArms]

I have a table of addresses which are presented like:

address1, address2, address3, address4
address1, NULL, address3, address4
address1, address2, NULL, address4
address1, NULL, NULL, address4
etc, etc!

I want the data to be extracted like so:

address1, NULL, address3, address4
BECOMES:
address1, ADDRESS2, address3, NULL
----
address1, NULL, NULL, address4
BECOMES:
address1, ADDRESS2, NULL, NULL
etc, etc

Note: each address is its own column not a string.

Another Note: I could do this with a case statement, however this will become very ugly, very quickly.

/Nice

 

[markros]

Looks like you need UNPIVOT and then PIVOT if you're using SQL Server 2005 or up.

 

[NiceArms]

Hi Markros,

I have done some reading on PIVOT and UNPIVOT and i'm not sure it is what i need (although I could be wrong).

Maybe i have explained myself poorly?

what is want is for all the column data to shift left as many columns as needed so:

address1, null , null, address4
becomes
address1, address4, null, null

(i have changed this from my previous post as it may have been misleading)

/Nice

 

[SQLSister]

There is no simple way to do this. Personally I think your best bet is to fix the bad data and then put rules in place so that bad data can't be entered again. A trigger would be the thing to do this properly.

Since you probably can't or won't do that, I suggest extracting your data to a temp table or table variable. Start with inserting records for address1, city, state,zip and the idfield.
Update the table inserting address 2 where address 1 is not null.
Then update the table inserting address 2 into address 1 if it is null and address 2 is not.
then do the same for three and four.

Or as you noted you could use the case statement.


"NOTHING is more important in a database than integrity." ESquared

 

[NiceArms]

Here is how I did it:

--Set address 1
UPDATE @addresstable SET Address_1 = Address_2 WHERE Address_1 = ''
--Set address 2 and update address 3
UPDATE @addresstable SET Address_2 = Address_3 WHERE Address_2 = ''
UPDATE @addresstable SET Address_3 = '' WHERE Address_2 = Address_3
UPDATE @addresstable SET Address_2 = Address_2 WHERE Address_2 = ''
--set address 3 and update address 4
UPDATE @addresstable SET Address_3 = Address_4 WHERE Address_3 = ''
UPDATE @addresstable SET Address_4 = '' WHERE Address_3 = Address_4
UPDATE @addresstable SET Address_3 = Address_3 WHERE Address_3 = ''