One of the new less publicized features in SQL Server 2017 is STRING_AGG. This new feature can take an expression of string values and concatenate them with a specified separator.
There’s not much more to say about it other than that really, so lets look at an example. Imagine we have a user table and we want to provide our application with a comma separated list of all the usernames in the table…
CREATE TABLE dbo.[User]
(
Id INT IDENTITY PRIMARY KEY,
Username NVARCHAR(20)
)
INSERT INTO dbo.[User](Username)
VALUES
('LukeCage'),
('JessicaJones'),
('BruceWayne')
SELECT STRING_AGG(ISNULL(Username,'Not Specified'),' , ')
FROM dbo.[User]
This kind of concatenation has always been a bit of a pain before SQL Server 2017, usually achieved using xml methods or variables and coalesce, now it couldnt be any easier.