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