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;
GO
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;
GO DROP TABLE #r |