IF OBJECT_ID('tempdb..#OriginalDataTable') IS NOT NULL DROP TABLE #OriginalDataTable
GO
CREATE TABLE #OriginalDataTable (RowID int, Address1 varchar(20), Address2 varchar(20), Address3 varchar(20), City varchar(10), State varchar(10))
INSERT INTO #OriginalDataTable (RowID, Address1, Address2, Address3, City, State)
VALUES
 (1, '1 Wall Street','Downtown','','New York','NY')
,(2, '2 Floor Street','Uptown','','New York','NY')
,(3, '','','','','')
,(4, '3 Ceiling Street','','','New York','NY')

SELECT
Address1 + ', ' + Address2 + ', ' + Address3 + ', ' + City + ', ' + State [Easy_But_Wrong]
,NULLIF(Address1 +
 CASE WHEN Address2 = '' THEN '' ELSE ', ' + Address2 END +
 CASE WHEN Address3 = '' THEN '' ELSE ', ' + Address3 END +
 CASE WHEN City = '' THEN '' ELSE ', ' + City END +
 CASE WHEN State = '' THEN '' ELSE ', ' + State END ,'') [Using_CASE]
FROM #OriginalDataTable