| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

T-SQL Create a Distinct Comma Separated List of Postal Codes
by BF (Principal Consultant; Architecture; Engineering)



SELECT @list= COALESCE(@list + ', ' ,'') + UPPER(REPLACE([PostalCode],' ', ''))
(SELECT DISTINCT(PostalCode) FROM dbo.ADDRESS WHERE [PostalCode] LIKE 'A%') t1

SELECT @list AS 'PostalCodes'

- Converts all the Postal Codes located in 1000's of table records into a single record/list
- Removes any empty spaces seen within a single postal code
- List is Distinct Postal Codes
- List is Upper Case

The advantage of coalese is if a column value is empty, it replaces that with an empty string and not an extra comma that
would likely need to be cleaned up/filtered out after.

Another common use case is dynamically generating a list of emails for the Business.