asked on

T-SQL to arrange Address in Certain Format

Want to be able to display a list of address from a table.
However because this will be used for a mail merge, I want to put some clever logic behing the code.

So ideally I would like all address to come back when they are all full as

Address 1, address 2, address 3, address 4, city, county

However if some of the fields are empty I want the next field to move up into its place so it wont have any blank space between each.

So if address 2 and address 3 were empty it would look like

Address 1, Address4, city, county

Can any one push me in the right direction please Ive tried to code it but with out much joy.

create table cm_customers_test
(id int, 
 address_1 varchar(30), 
 address_2 varchar(30),
 address_3 varchar(30), 
address_4 varchar(30), 
city varchar(30), 
county varchar(30))
 
INSERT INTO CM_CUSTOMERS_TEST values (99999999,'4 O Donoghue Park','', '', '','Bessbrook','Down')
 
 
 
--This is trying to make sure that values are not duplicated or any spaces are left between each field, 
--however its ok if there was a space if there was only 3 address full, to have a space after the
SELECT 
id as CUSTOMERNUMBER
,CASE WHEN address_1 = '' then address_2
			WHEN address_1 = ''and address_2 ='' then address_3 
			WHEN address_1 = '' and address_2 = '' and address_3 = '' then address_4
			WHEN address_1 = '' and address_2 = '' and address_3 = '' and address_4 = '' then city
			WHEN address_1 = '' and address_2 = '' and address_3 = '' and address_4 = '' and  city = '' then county Else Address_1 END ADDRESS_LINE_1
 
,CASE WHEN address_2  = address_1 then address_3 else address_2 End ADDRESS_LINE_2
 
,CASE WHEN address_3 = address_2 or address_3 = address_4 or  address_4 = city then county
      WHEN address_3 = address_2 or address_3 = address_4 or  address_4 = city or city = county then  county Else address_3 End ADDRESS_LINE_3
 
,CASE WHEN address_3 = address_4 or  address_4 = city or city = county then ''
      WHEN address_3 = address_4 or  address_4 = city then county  Else Address_4 END ADDRESS_LINE_4
 
,CASE WHEN address_4 = city or city = county then '' 
		WHEN address_4 = city or city = county then county ELSE City END City 
 
,CASE WHEN address_1 = county or address_2 = county or address_3 = county or address_4 = county or city = county  then ''
		Else County END COUNTY
 
 
FROM (
 
--This looks at a field and if its empty it will push the value up a field. 
 
select c.id
 
,case   when c.address_1 = '' then c.address_2
	    when c.address_1 = '' and c.address_2 = '' then c.address_3
		when c.address_1 = '' and c.address_2 = '' and c.address_3 = '' then c.address_4
		when c.address_1 = '' and c.address_2 = '' and c.address_3 = '' and c.address_4 = '' then c.city
		when c.address_1 = '' and c.address_2 = '' and c.address_3 = ''and c.address_4 = '' and c.city = '' then 
 
c.county Else c.Address_1 End Address_1
 
 
,case when c.address_2 = '' and c.address_3 = '' and c.address_4 = '' and c.city = '' then c.county 
	  when c.address_2 = '' and c.address_3 = '' and c.address_4 = '' then c.city
	  when c.address_2 = '' and c.address_3 = '' then c.address_4
	  When c.address_1 = c.address_2 or c.address_2 = '' then c.address_3 Else c.address_2 End Address_2
 
,case when c.address_3 = '' and c.address_4 = '' and  c.city = '' then c.county 
      when c.address_3 = '' and c.address_4 = '' then c.city
      when c.address_2 = c.address_3 or c.address_3 = '' then c.address_4  else c.address_3 End Address_3
	  
	  
	  
,case when c.address_4 = '' and c.city = '' then c.county 
	  when c.address_3 = c.address_4 or c.address_4 = '' then c.city else c.address_4 End Address_4
	  
	  
,case --when c.address_4 = c.city then ' '
      when c.city = '' and c.county = '' then Null 
      when c.address_4 = c.city or c.city = '' then c.county else c.city End City
	  
 
,case when c.city = c.county then ' '
	  when c.county = '' then Null Else c.county End County
	  
	  
from cm_customers_TEST c
where c.id = 99999999
)SUBQ

Open in new window

Putoch

ASKER

Ho there,
I've added some code to show you the type of logic i was trying to inplace, i hope it makes sence?

chapmandew

I would do something like this instead:

select
isnull([Address 1],'') + isnull([address 2], '') +isnull([address 3], '') +isnull([address 4], '') +isnull(city, '') +isnull(county '')
from tablename

Putoch

ASKER

Thank you chapmandew, but when i do this it only puts the data onto one row, trying to determine where each address enters into another is a problem.
Currently the result returns as '4 O Donoghue ParkBessbrookDown'

I would prefer to have each address on a different colunm if you get me as i need to automatically let the report run and export to excel rather then manually copying and pasting to text to colum if you get me?

chapmandew

Would it not be just as easy to have the address in one column, rather than 4?

Putoch

ASKER

You see, it will be used for a mail merge so it would be better suited for the people using it to have it on seperate colunms.

thanks,
putoch

Auric1983

Address 1, address 2, address 3, address 4, city, county

select isnull([Address 1],'') as Line1, isnull([Address 2],isnull([Address3],[Address4]) as Line2,isnull([Address3],[Address4]) as Line3,City,County

Putoch

ASKER

thanks Auric, however that just gives me teh lineup that i have already.
The address still have lines blank in bteween addres where there is no infomation instead of moving the info into the blank cells.

So when you run that query on the table i created it still comes out as
LINE 1, LINE 2, LINE 3, CITY, COUNTY
# , blank , blank, # , #

select isnull([Address_1],'') as Line1
,isnull([Address_2],isnull([Address_3],[Address_4])) as Line2
,isnull([Address_3],[Address_4]) as Line_3
,City
,County
From cm_customers_test

Rather then
LINE 1, LINE 2, LINE 3, CITY, COUNTY
# ,# , # ,blank, blank

Thanks

Auric1983

Putoch,

You could extend my logic a little more and do something like the following.

Select isnull([Address1],isnull([address 2],isnull([address3], isnull([city],county) as Line1, isnull([address 2],isnull([address3], isnull([city],county)
as line2, isnull([address3], isnull([city],county) as Line3, isnull([city],county) as line4, isnull(county,'') as line5

i didn't realize that you wanted to move the City & County fields up to the line 2 and 3 position.

I've only had one cup of coffee so far this AM :D

Auric1983

the isnull function is sort of line an "in line if" statement.

If fieldname is null then '' else fieldname. so by nesting them to say if my first field is empty, use the second, if the second is empty use the third etc. that should give you what you want.

Putoch

ASKER

Thank Auric, its still not working. Its giving me back what i am already getting.
Select isnull([Address1],isnull([address 2],isnull([address3], isnull([city],county) as Line1, isnull([address 2],isnull([address3], isnull([city],county)
as line2, isnull([address3], isnull([city],county) as Line3, isnull([city],county) as line4, isnull(county,'') as line5
With a space in between the colums with nothing in them when they have nothing or blank as an entry
Or if they have NULL in the colum the same line for Line 2/3/4 appears.

chapmandew

I don't understand what you want...

Anthony Perkins

SQL Server was never intended to do this. That is the function of Report Writers. However, if you are insisting you will have to do something like this:

Select      CASE
                  WHEN ISNULL(Address1, '') = '' THEN
                        CASE
                              WHEN ISNULL(Address2, '') = '' THEN
                                    CASE
                                          WHEN ISNULL(Address3, '') = '' THEN
                                                CASE
                                                      WHEN ISNULL(Address4, '') = '' THEN Null
                                                      ELSE Address4
                                                END
                                          ELSE Address3
                                    END
                              ELSE Address2
                        END
                  ELSE Address1
            END Address1,

            CASE
                  WHEN ISNULL(Address2, '') = '' And ISNULL(Address1, '') <> '' THEN
                        CASE
                              WHEN ISNULL(Address3, '') = '' THEN
                                    CASE
                                          WHEN ISNULL(Address4, '') = '' THEN Null
                                          ELSE Address4
                                    END
                              ELSE Address3
                        END
                  ELSE Address2
            END Address2,

            CASE
                  WHEN ISNULL(Address3, '') = '' And ISNULL(Address2, '') <> '' And ISNULL(Address1, '') <> '' THEN
                        CASE
                              WHEN ISNULL(Address4, '') = '' THEN Null
                              ELSE Address4
                        END
                  ELSE Address3
            END Address3,

            CASE
                  WHEN ISNULL(Address3, '') <> '' And ISNULL(Address2, '') <> '' And ISNULL(Address1, '') <> '' THEN Address4
                  ELSE ''
            END Address4
From ...

Putoch

ASKER

Thank you for this however it doesn't work the way i need it to it is still bringing back gaps between address when there is blank values in them.
Ideally i want the syntax to return this example :
Address1 , address2 , address3, address4, city, County
no2 , , the hill, , dub, dublin

the format i want it to return is:
Address 1, address2, address3, address4 , City, county
No2, the hill , dub ,dublin , ,

So you can see that whenever there is a blank field to the left the field the the right should fill it.

You say this can be done using report manager? how is this so? if it can be done with it i can use that instead of just SQL ?

thanks for all your help

Auric1983

Putoch, what are you using to create the report?

Putoch

ASKER

Well usually i write the code in t-sql and then put this into the Report Serviese tool to create the report.
ssrs, SQL SERVER 2005

Auric1983

Depending on my requirements I usually do a bit of both...

in SSRS are you concatinating all of the fields together or do you have 5 objects?

ASKER CERTIFIED SOLUTION

Truzenzex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Putoch

ASKER

Thanks everyone for your help on this.
Yes i did want to bring back 5 objects say and not group the whole fields together.
However Truzenzex has given me back a script that i can use, it ignors the blanks, and perhaps if i export the data into a CSV when the person opens it they can seperate the words by each comma and that would give it the effect i'm looking for?

However if any one was able to give me back something similar to Truxenzex results but instead of having the results in one colum seperated by a comma they were in a differnt cell each that would be fab. thanks everyone for your help.
Putoch!!