How string_agg function and WITHIN GROUP works

Sudip Bhatt 2,271 Reputation points
2020-12-05T15:56:53.06+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2020-12-05T16:56:00.393+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Joe Celko 16 Reputation points
    2020-12-05T18:34:51.337+00:00

    > 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?


  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-07T02:55:32.993+00:00

    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:
    45601-1.png
    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:
    45523-2.png
    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:
    45592-3.png

    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

    1 person found this answer helpful.

  3. Tom Cooper 8,466 Reputation points
    2020-12-07T16:16:31.727+00:00

    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

    1 person found this answer helpful.
    0 comments No comments