SQL Server STRING_AGG Function

SQL Server STRING_AGG Function is string function which concatenates values for any given string expressions and separate the string by specified separator.




  • String_Agg function was introduced in SQL Server 2017.
  • It does not add the separator at the end of the result string.
  • String expression values are implicitly converted to string types and then concatenated.

SYNTAX

STRING_AGG ( expression, separator ) [ <order_clause> ] 

expression is any type that can be converted to VARCHAR and NAVARCHAR during the concatenation.

separator is the separator for the concatenated string. It can be a VARCHAR and NVARCHAR type.

order_clause to sort the order of concatenated string using WITHIN Group clause:

The return type of STRING_AGG function is depends on first argument (expression). If input argument is string type VARCHAR and NAVARCHAR then result type will be same as input type.

Lets take an example of STRING_AGG Function in SQL Server.

To demonstrate an example, first we will create a sample table named PhoneNoList then insert some dummy records in table.

CREATE TABLE dbo.PhoneNoList( 
PhoneId INT,
UserId INT, 
PhoneNo VARCHAR(50)
)

Lets insert records in table.

 INSERT INTO PhoneNoList 
(PhoneId, UserId, PhoneNo)
VALUES
(1, 501,'9808090909'),
(2, 501,'011346456'),
(3, 501,'6180808087'),
(4, 501,'6767678975'),
(5, 502,'78987898788'),
(6, 502,'011546456'),
(7, 503,'2345353256'),
(8, 503,'89089089079'),
(9, 504,'03453545'),
(10, 504,'9180872323'),
(11, 504,'001123543'),
(12, 504,'9079657565')

As you can see in below output of table, a single user can have multiple phone numbers.

For example, for UserId =501 there are four phone numbers and userId =2, there are two phone numbers, in similar way other users are having multiple phone number.

[Read Also SQL Server STRING_SPLIT() Function ]

Lets assume, you want to display these multiple phone numbers of each user into single column by separating each phone numbers with comma.

You can use STRING_AGG function, following T-SQL query uses STRING_AGG function which concatenate the rows of phone numbers for each user into one single string with separated by comma.

Select UserId , String_Agg(PhoneNo,',') AS PhoneNoLists 
FROM PhoneNoList 
GROUP BY UserId

 

You can also sort the order of concatenated string by using WITHIN GROUP clause in STRING_AGG Function.

Sorting the string in ascending order

By default Order by clause sort the result in ascending order. So its an optional either specifying keyword ASC or not with Keyword Order By.

Following T-SQL query sorts the phone numbers string in ascending order.



Select UserId , 
String_Agg(PhoneNo,', ') WITHIN GROUP(ORDER BY PhoneNo ASC) AS PhoneNoLists
FROM PhoneNoList
GROUP BY UserId

Sorting the string in descending order

Following T-SQL query sorts the phone numbers string in descending order.

Select UserId, 
String_Agg(PhoneNo,', ') WITHIN GROUP(ORDER BY PhoneNo DESC) AS PhoneNoLists
FROM PhoneNoList
GROUP BY UserId

If you are not using SQL Server 2017 or later version, then you can use below T-SQL to get the same output.

SELECT userId, STUFF((
SELECT N', ' + PhoneNo FROM PhoneNoList
WHERE UserId = x.UserId
ORDER BY UserId
FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')AS PhoneNoList
FROM PhoneNoList AS x
GROUP BY USerId

 

Read Also..

SQL Server STRING_SPLIT() Function




SQL Server CONCAT_WS() Function

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 1,416 total views,  5 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.