SQL group results as a nested array with aggregated values-CodePudding

I have a query that returns me SUM amount per column for specific column 'EUR'

select sum(DC_SUB.REM_AMOUNT)
                       from DEBT.DEBT_CLAIM DC_SUB
                       where DC_SUB.DEBT_ID = D.DEBT_ID
                         and DC_SUB.END_DATE is null
                         and DC_SUB.CUR_CODE = 'EUR'
                         and DC_SUB.STATUS = 'ACTIVE') as REM_AMOUNT_EUR,

result:

REMAINING_AMOUNT_EUR
68837.22

Instead of writing query for each Currency 'USD', 'CAD', I would like to aggregate it to one column as nested array and then easily parse that values on UI.

Desired result in one column:

REMAINING_AMOUNT 
[['EUR', 68837.22],['USD', 1233.12], ...]

I approached it with group by, but it still does not return the format I want

select DC_SUB.CUR_CODE, sum(DC_SUB.REM_AMOUNT)
 from DEBT.DEBT_CLAIM DC_SUB
 where DC_SUB.DEBT_ID = D.DEBT_ID
   and DC_SUB.END_DATE is null
   and DC_SUB.STATUS = 'ACTIVE'
 group by DC_SUB.CUR_CODE as GROUPPED_CUR

CodePudding user response：

This is achievable using STUFF function in SQLServer.

select top 1
CUR_CODE = 
 stuff((SELECT ',['''  DC_SUB.CUR_CODE   ''','  convert(varchar, sum(DC_SUB.REM_AMOUNT))  ']'
 from DEBT.DEBT_CLAIM DC_SUB
 where DC_SUB.DEBT_ID = D.DEBT_ID
   and DC_SUB.END_DATE is null
   and DC_SUB.STATUS = 'ACTIVE'
 group by DC_SUB.CUR_CODE
for xml path('') 
), 1, 1,'') 
from DEBT.DEBT_CLAIM

CodePudding user response：

Also using XML PATH variant. Constructs the pairs then aggregates them with XML.

SELECT SUBSTRING((  
       SELECT ',['   DC_SUB.CUR_CODE   ','   CAST(SUM(DC_SUB.REM_AMOUNT) AS    VARCHAR(20))   ']'
       FROM DEBT.DEBT_CLAIM DC_SUB
       WHERE DC_SUB.DEBT_ID = D.DEBT_ID
         AND DC_SUB.END_DATE is null
         AND DC_SUB.STATUS = 'ACTIVE'
       GROUP BY CUR_CODE
       FOR XML PATH(''), TYPE
    ).value('text()[1]', 'nvarchar(max)'), 
    2,1000
) [CUR_CODEs];