Binaryworld - Concat several address fields together (example) ... [ SQL Server -> T-SQL ]

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.

Click here to copy the following block

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 );
SELECT * FROM #r;
/*
ID     Address_1     Address_2 Address_3
----------- ----------------- --------- ----------
     1 NULL       Some   Avenue
     2 1400       NULL   Avenue
     3 1400       Some   Avenue
     4 1400 Some Avenue NULL   NULL
     5 NULL       NULL   P.O 130
     6 960A       NULL   NULL
     7 NULL       POB    NULL
*/
GO

/*
    The number of expressions in the coalesce function will be
    (2^n - 1) where n is the number of fields to be concatenated.
    This method shows how to concatenate several columns using a
    delimiter. The concatenation handles NULLs , empty strings etc.
    This case be written as CASE statement too.
*/
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;
/*
ID     Address
----------- -----------------
     1 Some,Avenue
     2 1400,Avenue
     3 1400,Some,Avenue
     4 1400 Some Avenue
     5 P.O 130
     6 960A
     7 POB
*/
GO
DROP TABLE #r