| 437-991-3573 | Data Tier Services
Explore Tools
Experts in Microsoft SQL Server, Clusters, Always On, Migrations, Performance, & Cloud

T-SQL Create a Distinct Comma Separated List of Postal Codes
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)



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.