google sheets - Concatenating from a table by filtering first

Question

Consider the table below:

       A             B            C        D
 1 category      subcategory    company  amount
 2 health care   diagnostics     AA       100
 3 health care   diagnostics     AB        50
 4 materials     mining          BA        75
 5 financials    banks           CA        30
 6 financials    insurers        CB        35
 7 financials    banks           CC        10
 8 financials    banks           CD        40
 9 financials    hedge fund      CE         5
10 health care   equipment       DA        50

I want to (1) concatenate the companies for each category and (2) return the aggregated amount.

      A              B              C
1 category       companies       amount
2 health care    AA AB DA          200     <--- 100 + 50 + 50
3 materials      BA                 75
4 financials     CA CB CC CD CE    120

I am aware that (2) can easily be done by using a pivot table, but (1) is not supported by pivots. So given the excel below, what formula do I need in B1 and C1 to get the desired output?

      A              B              C
1 category       
2 health care    
3 materials      
4 financials     

I have looked into concatenate(), which is needed here, but I don't see how I can give arguments to that function, like pseudo:

=concatenate(C2:C10; "where (A:A) = 'health care')

Who can help me out?

question from:https://stackoverflow.com/questions/65836751/concatenating-from-a-table-by-filtering-first

Answer 1

=ARRAYFORMULA(QUERY({QUERY({A:D}, 
 "select Col1,sum(Col4) 
  where Col1 is not null 
  group by Col1 
  label sum(Col4)'amount'", 1), {"companies"; 
 TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY({A:D, C:C}, 
 "select max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col5"), 
 "offset 1", 0)),,9^9)))}}, 
 "select Col1,Col3,Col2", 1))