Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



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








T-SQL:


DECLARE @list VARCHAR(MAX)

SELECT @list= COALESCE(@list + ', ' ,'') + UPPER(REPLACE([PostalCode],' ', ''))
FROM
(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.