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

PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PutochAuthor Commented:
Ho there,
I've added some code to show you the type of logic i was trying to inplace, i hope it makes sence?
0
chapmandewCommented:
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
0
PutochAuthor Commented:
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?

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

chapmandewCommented:
Would it not be just as easy to have the address in one column, rather than 4?
0
PutochAuthor Commented:
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
0
Auric1983Commented:
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
0
PutochAuthor Commented:
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
0
Auric1983Commented:

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


0
Auric1983Commented:
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.

0
PutochAuthor Commented:
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.
0
chapmandewCommented:
I don't understand what you want...
0
Anthony PerkinsCommented:
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 ...
0
PutochAuthor Commented:
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
0
Auric1983Commented:
Putoch, what are you using to create the report?
0
PutochAuthor Commented:
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
0
Auric1983Commented:
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?
0
TruzenzexCommented:
This is what I use...
CASE WHEN LTRIM(RTRIM(strAddress1)) 
                      <> '' THEN strAddress1 ELSE '' END + CASE WHEN LTRIM(RTRIM(strAddress2)) <> '' THEN ', ' + strAddress2 ELSE '' END + CASE WHEN LTRIM(RTRIM(strAddress3)) 
                      <> '' THEN ', ' + strAddress3 ELSE '' END + CASE WHEN LTRIM(RTRIM(strAddress4)) 
                      <> '' THEN ', ' + strAddress4 ELSE '' END + CASE WHEN LTRIM(RTRIM(strPostcode)) <> '' THEN ', ' + strPostcode ELSE '' END AS [Contact Address]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
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!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.