How to Concatenate Columns To CSV String?

In our Property table we have addresses in different fields.  A full address field is used for the ease of report generation.  This field is a concatenation of the different address fields.  Sometimes the fields are NULL or empty.  I use the following code to create CSV string:

+ coalesce(','nullif(STREETDSC,''),'')
+ coalesce(','nullif([LOCALITY1],''),'')
+ coalesce(',',nullif([LOCALITY2],''),'')
+ coalesce(',',nullif([POSTCODE],''),'')
from tmppropertyimport

I am using COALESCE and NULLIF for this purpose.

In short COALESCE returns the first non-null expression from its parameter(s).

NULLIFF returns a null value if the two specified expressions are equal.

Lets look at how it works.

NULLIF(PROPNUM, ‘’) – returns PROPNUM if not null or not empty.  Returns NULL if PROPNUM is  NULL or empty.

‘,’ + NULLIF(PROPNUM, ‘’) – returns “,PROPNUM” if PROPNUM has data.  Returns NULL if PROPNUM is null or empty.

The result is CSV string beginning with a comma.  I get rid of that comma using STUFF(string,1,1,’’).

Thanks for this goes to Stackoverflow post: