SQL group results as a nested array with aggregated values

Question

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

Ed Bangga · Accepted Answer · 2022-04-21 08:58:55Z

1

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

edited Apr 21, 2022 at 8:58

answered Apr 21, 2022 at 8:25

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Golovinov Over a year ago

when I try to select this column it says: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. How to select it using group by clause?

Ed Bangga Over a year ago

if you add columns, add those also on the group by

houstonrahoyt · Accepted Answer · 2022-04-21 08:59:45Z

0

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];

answered Apr 21, 2022 at 8:59

houstonrahoyt

844 bronze badges

2 Comments

jarlh Over a year ago

Product specific answer to a question with no dbms specified. At least tell us which dbms this is for.

houstonrahoyt Over a year ago

DBMS is SQL Server.

Collectives™ on Stack Overflow

SQL group results as a nested array with aggregated values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related