How string_agg function and WITHIN GROUP works

Question

I got an answer from this url https://learn.microsoft.com/en-us/answers/questions/185006/how-to-find-amp-replace-separated-value.html?childToView=185666#answer-185666

there string_agg function used and WITHIN GROUP also used.

please discuss the usage of string_agg function and WITHIN GROUP with a easy example as a result i can understand when and where to use it.

Thanks

Answer 1

String_Agg combines the values from a group one or more rows into a delimited list. For example, suppose you have a table like

Declare @Sample Table(CustomerID int, OrderID int)
Insert @Sample(CustomerID, OrderID) Values
(100, 75),
(210, 37),
(100, 47),
(330, 235),
(100, 255),
(210, 14);

Then you could get a comma delimited list of of each customer's orders by using String_Agg with

Select CustomerID, String_Agg(OrderID, ', ') As Orders
From @Sample
Group By CustomerID
Order By CustomerID;

The result would look like

100 75, 47, 255
210 14, 37
330 235

That gives a list of each customer's orders. But the list is in no particular order. If you the list to be in a given order, you would use Within Group. For example

Select CustomerID, String_Agg(OrderID, ', ') Within Group(Order By OrderID) As Orders
From @Sample
Group By CustomerID
Order By CustomerID;

Then the result would be

100 47, 75, 255
210 14, 37
330 235

Tom

Answer 2

> please discuss the usage of string_agg function and WITHIN GROUP with an easy example as a result I can understand when and where to use it. <<

This sort of thing violates first normal form and the very foundations of relational databases. Dr. Codd's work was based on columns holding scalar values. Each value is supposed to be atomic. This should been covered in the first week of the class on RDBMS.

If you don't like this, then you should be using a non-first normal form (NFNF) database, one of the multivalued products that are available to you, such as the PICK operating system. You're going to wind up with a lot of bad habits and poor performance when you try to use SQL for this sort of thing. Much of the work on these non-SQL databases were done at the University of Texas by Roth and Korth. I have no idea why Microsoft would add such a thing to its product. Also, why do you want to be a bad SQL programmer?

Answer 3

Hi @Sudip Bhatt ,

Suppose we have below table:

CREATE TABLE dbo.PhoneNoList(   
UserId INT,   
PhoneNo VARCHAR(50)  
)  
  
INSERT INTO PhoneNoList   
VALUES  
(501,'9808090909'),  
(501,'011346456'),  
(501,'6180808087'),  
(501,'6767678975'),  
(502,'78987898788'),  
(502,'011546464'),  
(503,'2345353256'),  
(503,'89089089079')  

We could use STRING_AGG to combine the value of phoneno and email using below query:

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

Output:

As you could see the output above, the phoneno is unsorted. If we would like to make it sorted, we firstly try to add order by at the end of this query as below:

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

But we would receive one error as below:
Msg 8127, Level 16, State 1, Line 30
Column "PhoneNoList.PhoneNo" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

After some modifications, we would not get any error but the output is not desired one.

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

Output:

In this situation, we could use WITHIN GROUP to get the expected output.

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

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Before String_Agg was available (it was added in SQL 2017), you could do

Declare @Sample Table(CustomerID int, OrderID int)
Insert @Sample(CustomerID, OrderID) Values
(100, 75),
(210, 37),
(100, 47),
(330, 235),
(100, 255),
(210, 14);

Select Distinct s.CustomerID,
  (Select Stuff(
    (Select ', ' + Cast(a.OrderID As varchar(11)) From @Sample a Where a.CustomerID = s.CustomerID Order By a.OrderID 
    For XML Path(''),Type)
    .value('text()[1]','varchar(max)'),1,2,N'')) As Column1Dataa
From @Sample s
Order By s.CustomerID;

Tom