Concatenate Address Columns In One Column

In many databases the address is properly separated into various columns but in a report you want to concatenate them.

This simple script will pull them together in one column without causing extra spaces

SELECT ltrim(isnull([address], '') + ' ' + isnull([address2], '') + ' ' + isnull([city], '') + ' ' + isnull([state], '') + ' ' + isnull([zip], '')) AS "Address"
FROM table

SELECT ltrim(CONCAT_WS('', p.firstname, ' ', p.middlename, ' ', p.lastname)) AS 'Patient Name'
FROM patient p;