In SQL Server DB how to add a comma between Address Details

Suppose we have a condition to add comma between Address1, Address2, City, State, and Zip But add comma only when value is available on that particular column.

 E.g. we have one table i.e. AddressDetails as shown below

Id	Address1	Address2	City	State	Zip
101	5 Wesley	Hermosa	Johnson	Tennessee	123
102	2301 MCLain		New Madison		456

 

Query:

select  Id,

ISNULL(NULLIF(Address1, '') + ',', '') +

ISNULL(NULLIF(Address2, '') + ',', '') +

ISNULL(NULLIF(City, '') + ',', '') +

ISNULL(NULLIF(State, '') + ',', '') +

ISNULL(NULLIF(Zip, '') + '', '')-----Remove last column comma

As Address

from AddressDetails

O/P:-

Id	Address
101	5 Wesley, Hermosa, Johnson,  Tennessee, 123
102	2301 MCLain, New Madison, 456

Here for Id=102 Address2 and State column is empty but still no extra column was added into the result set.

 

What other ideas can you add to this post that I may have not mentioned?