String_Agg concatenating duplicated value when there is multiple Ids exist

Question

Table

Claim_Id    Service_Id  Service_Line_Id     col  
------------------------------------------------
1599316     H2012       1653805             
1599316     H2012       1653805             HE
1599316     H2012       1653805             TG

1599316     H2012       1653806 
1599316     H2012       1653806             HE
1599316     H2012       1653806             TG

I'm trying to concatenate the values to become grouped by servicelineId, but it's appearing like this:

claim_id    Service_Line_Id concatenatedvalue   Service_Id  procedure_modifier1 procedure_modifier2 procedure_modifier3 procedure_modifier4
-------------------------------------------------------------------------------------------------------------------------------------------
1599316     1653805         H2012HEHETGTG       H2012           HE                      TG                                      
1599316     1653806         H2012HEHETGTG       H2012           HE                      TG                                      

But expected result in concatenatedvalue H2012HETG like will be like

claim_id    Service_Line_Id concatenatedvalue   Service_Id  procedure_modifier1 procedure_modifier2 procedure_modifier3 procedure_modifier4
-------------------------------------------------------------------------------------------------------------------------------------------
1599316     1653805         H2012HETG           H2012           HE                      TG                                      
1599316     1653806         H2012HETG           H2012           HE                      TG

                                    

I'm using the like below query to achieve the results, looks I'm missing the group logic in area

CONCAT(unioncte.Service_Id, STRING_AGG(col, '') WITHIN GROUP (ORDER BY col)) AS  concatenatedvalue


SELECT
    unioncte.claim_id,
    CONCAT(unioncte.Service_Id, STRING_AGG(col, '') WITHIN GROUP (ORDER BY col)) AS concatenatedvalue,
    unioncte.Service_Id   
FROM 
    unioncte        
GROUP BY  
    unioncte.Claim_Id, unioncte.Service_Id, 
    procedure_modifier1, procedure_modifier2, 
    procedure_modifier3, procedure_modifier4

Answer 1

You need Service_Line_Id in the GROUP BY clause. You can also simplify the STRING_AGG call.

SELECT
    claim_id,
    Service_Line_Id,
    CONCAT(Service_Id, STRING_AGG(col, '')) AS concatenatedvalue,
    Service_Id   
FROM 
    unioncte        
GROUP BY  
    Claim_Id, Service_Line_Id,Service_Id

fiddle

claim_id	Service_Line_Id	concatenatedvalue	Service_Id
1599316	1653805	H2012HETG	H2012
1599316	1653806	H2012HETG	H2012