How to combine separate row values into delimited string

In one of my old post, I wrote about how to combine separate row values into delimited string. Now starting from SQL Server 2017, new string function was introduced to perform this string aggregation easily. Let us see few examples in this post.

DECLARE @Tmp TABLE (ID INT,
                    Name VARCHAR(30))
INSERT INTO @Tmp SELECT 1,'SQL Server'
INSERT INTO @Tmp SELECT 2,'Oracle'
INSERT INTO @Tmp SELECT 3,'DB2'
SELECT * FROM @Tmp

SELECT STRING_AGG (Name, ',') FROM @Tmp

We can also order the concatenated values as shown in below example:

--To order the concatenated values
SELECT STRING_AGG (Name, ',') WITHIN GROUP (ORDER BY Name ASC) AS DBNames  FROM @Tmp



Example on how to combine values from separate grouped records into delimited string.




DECLARE @Tmp TABLE (ID INT,
                    DBName VARCHAR(30))
INSERT INTO @Tmp SELECT 1,'SQL Server 2005'
INSERT INTO @Tmp SELECT 1,'SQL Server 2008'
INSERT INTO @Tmp SELECT 1,'SQL Server 2008R2'
INSERT INTO @Tmp SELECT 1,'SQL Server 2012'
INSERT INTO @Tmp SELECT 2,'Oracle 10g'
INSERT INTO @Tmp SELECT 2,'Oracle 11g'
INSERT INTO @Tmp SELECT 2,'Oracle 12c'
INSERT INTO @Tmp SELECT 3,'DB2 v9.7'
INSERT INTO @Tmp SELECT 3,'DB2 v9.8'

SELECT ID, STRING_AGG (DBName, '|') DBName FROM @Tmp
GROUP BY ID





Reference: 



See Also: 


No comments: