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.
We can also order the concatenated values as shown in below example:
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
No comments:
Post a Comment