I could be completely wrong here but are you sure you do not want result set in one column ? And want it to spread across multiple columns ? If you want it in one column then PFB else you may want to try what ChrisM@Work has suggested ....

CREATE TABLE #TEMP

(

ROWID VARCHAR(100),

Address1 VARCHAR(100),

Address2 VARCHAR(100),

Address3 VARCHAR(100),

Address4 VARCHAR(100),

Postcode VARCHAR(100)

)

INSERT INTO #TEMP

SELECT 'ABC123', '13 This St','' ,'' , 'MySuburb', '21334' UNION ALL

SELECT'ABC123', '','10 Another Rd','Building X','Smithville','34563' UNION ALL

SELECT'ABC123', 'Unit 3', '','10 Smith St',' Jonesville',' 4356' UNION ALL

SELECT'BCD987', '3 My Road','' ,'' , 'SuburbX', '234556' UNION ALL

SELECT'BCD987', '14 Long Rd',' Unit 21','', 'Badtown', '11223' UNION ALL

SELECT 'DJU765', '2 Short St', '', '', 'MySuburb', '21334'

SELECT * FROM #TEMP

SELECT ROWID,STUFF((

SELECT ',' + Address1 + ',' + Address2 + ',' + Address3 + ',' + Address4 + ',' + Postcode FROM #TEMP INNER_TAB

WHERE INNER_TAB.ROWID = OUTER_TAB.ROWID

FOR XML PATH('')),1,1,'') FROM #TEMP OUTER_TAB

GROUP BY ROWID

DROP TABLE #TEMP