Address concatenation

Is this what you're looking for?

 CREATE TABLE #temp (
  id INT
  , NAME VARCHAR(50)
  , addr1 VARCHAR(50)
  , addr2 VARCHAR(50)
  , addr3 VARCHAR(50)
  , city VARCHAR(50)
  , STATE VARCHAR(50)
  )
 GO
 
 INSERT #temp (
  id, NAME, addr1, addr2, addr3, city, STATE
  )
 SELECT 1, 'aaaa', 'xxxx', 'yyyy', 'zzzz', 'tttt', 'rr'
 UNION
 SELECT 2, 'bbb', 'yyyy', 'zzzz', 'aaaa', 'jjjj', 'rr'
 GO
 
 SELECT id
  , NAME
  , addr1 + ' ' + addr2 + ' ' + addr3 + ' ' + city + ' ' + STATE AS 'Address'
 FROM #temp
 GO