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.
|
|
|
|
|
|