Comma-separated string via COALESCE

				
--**************************************
-- Name: Comma-separated string via COALESCE
-- Description:Concatenating several column values as a comma-separated string is a often encountered scenario. This method shows one way to do this using COALESCE function.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=193&lngWId=5--for details.--**************************************

create table #r
(
 ID int IDENTITY (1, 1) primary key,
 Address_1 varchar (100),
 Address_2 varchar (100),
 Address_3 varchar (100)
)
go
insert into #r values ( null, 'Some', 'Avenue' )
insert into #r values ( '1400', null , 'Avenue' )
insert into #r values ( '1400', 'Some', 'Avenue' )
insert into #r values ( '1400 Some Avenue', NULL, NULL )
insert into #r values ( null, null, 'P.O 130' )
insert into #r values ( '960A', null, null )
insert into #r values ( null, 'POB', null )
-- The number of expressions in the coalesce function will be
-- (2^n - 1) where n is the number of fields to be concatenated
select ID, coalesce(
	NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
	NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') ,
	NULLIF ( address_1, '') + ',' + NULLIF ( address_3, '') ,
	NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
	NULLIF ( address_1, '') ,
	NULLIF ( address_2, '') ,
	NULLIF ( address_3, '') ,
	'' ) as Address
from #r
drop table #r