how to show multiple fields as a single field separated by comma

I wanted to concatinate multiple address fields as a single field
separated by comma. Here is the solution which is doing job for me.

8000 is the Max length of varchar data type in sql server.
Using COALESCE() function saves  lot of IF or CASE decision logics.
This function Returns the first nonnull expression among its arguments
If all arguments are NULL, COALESCE returns NULL
Substring() function takes 3 arguments here first argument is Address
2 is starting point and 8000 is lenght of charcaters to return. If start it
from 1 instead of 2 then if First filed is null then it will put “,” in start
which definitely is not required. So not to srat with comma I am putting start
from 2nd charachter of the string.
coalesce(‘,’ + department_campus,”) +
coalesce(‘,’ + address1,”) +
coalesce(‘,’ + address2,”) +
coalesce(‘,’ + address3,”)+
coalesce(‘,’ + town,”)+
coalesce(‘,’ + county,”),
 2, 8000) as Address

Tags: , , , , ,

Comments are closed.