Yet another SQL Server enthusiast

2017-07-31

SQL Server 2017 New Features: STRING_AGG

Filed under: Uncategorized — sa @ 12:53 PM

If SQL Server 2016 introduced STRING_SPLIT, SQL Server 2017 comes with STRING_AGG:

STRING_AGG ( expression, separator ) 

[ WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]

This function should be used to concatenate string ([N]VARCHAR(n)) values on columns thus:

DECLARE @Products TABLE (
 ProductID INT PRIMARY KEY,
 ProductName NVARCHAR(50) NOT NULL,
 Category NVARCHAR(50) NOT NULL
);
INSERT @Products (ProductID, ProductName, Category)
VALUES 
 (1, 'Windows 7', 'OS'), 
 (2, 'Windows 10', 'OS'), 
 (3, 'Ubuntu 17.04', 'OS'),
 (4, 'SQL', 'Programming language'),
 (5, 'C#', 'Programming language');

SELECT STRING_AGG(v.ProductName, ', ') AS Agg1_NoOrder
FROM @Products AS v

SELECT STRING_AGG(v.ProductName, ', ') WITHIN GROUP (ORDER BY v.ProductName) AS Agg1_ASC
FROM @Products AS v

SELECT STRING_AGG(v.ProductName, ', ') WITHIN GROUP (ORDER BY v.ProductName DESC) AS Agg1_DESC
FROM @Products AS v

SELECT v.Category, STRING_AGG(v.ProductName, ', ') AS Agg2_GroupBy
FROM @Products AS v
GROUP BY v.Category

Results:

sql.server.string_agg.examples.within_group

Please do not confuse this new functionality with string concatenations on rows using + operator or using CONCAT / CONCAT_WS – concat with separator functions:

SELECT 'Windows 7' + ', ' + 'Windows 10' + ', ' + 'Ubuntu 17.04' AS Concat1
SELECT CONCAT('Windows 7', ', ', 'Windows 10', ', ', 'Ubuntu 17.04') AS Concat2
SELECT CONCAT_WS(', ', 'Windows 7', 'Windows 10', 'Ubuntu 17.04') AS Concat3

Results:

sql.server.string.concatenation

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: