Home Can a 4 column address be merged into a 2 columns address with a SELECT statement?

Questions

Can a 4 column address be merged into a 2 columns address with a SELECT statement?

May 2, 2024

I am using SQL Server and moving data from an address table with the following layout

ADDRLine1
ADDRLine2
ADDRLine3
ADDRLine4
ADDRCity
ADDRCounty
ADDRPostcode

Into another database that only has two address fields

ADDRLine1
ADDRLine2
ADDRCity
ADDRCounty
ADDRPostcode

The fields need to be mapped sensibly like this:

INPUT                            |OUTPUT                  
Add1    |Add2    | Add3  |Add4   |Add1        |Add2       
----------------------------------------------------------
Filled  |Filled  |Filled |Filled |Add1, Add2  |Add3, Add4 
Filled  |Filled  |Filled |NULL   |Add1, Add2  |Add3       
Filled  |Filled  |NULL   |NULL   |Add1        |Add2       
Filled  |NULL    |NULL   |NULL   |Add1        |

Can this be done with a SELECT statement so that I can use it in a view?

I am trying to use IF or CASE in order to control the mapping logic but am struggling to understand how I can use either IF or CASE in such way as to achieve the end result.

With concatenation there is still a requirement to branch the output according to which columns are NOT NULL.

Using a Function or Stored Procedure would allow for more logical processing and the use of a concatenation but if there is a solution available in a SELECT statement it would allow me to implement it within a VIEW.

>Solution :

This should do it, based on the examples where earlier address will always populate before later (ie: you’ll never have a value in ADDRLine4 if ADDRLine1 is NULL):

ADDRLine1 + COALESCE(CASE WHEN coalesce(ADDRLine3, ADDRLine4) IS NOT NULL THEN ', ' + ADDRLine2 END,'') 
    AS Add1,
CASE WHEN coalesce(ADDRLine3, ADDRLine4) IS NULL THEN ADDRLine2 
     ELSE ADDRLine3 + COALESCE(', ' + ADDRLine4, '') END 
   AS Add2

You can also use CONCAT_WS, but you still need some CASE expressions because ADDRLine2 may be in a different column depending on whether later address lines have a value:

WS_CONCAT(', ', ADDRLine1, CASE WHEN coalesce(ADDRLine3, ADDRLine4) IS NOT NULL THEN ADDRLine2 END) Add1
WS_CONCAT(', ', CASE WHEN COALESCE(ADDRLine3, ADDRLine4) IS NULL THEN ADDRLine2 END, ADDRLine3, ADDRLine4) Add2