Merging two lists into one

Question

I have two different lists. A list of opportunities and a list of sales (Umsatz__c), the accountid appears in both lists. How can I merge these lists into one finallist at account level? This list should

Opp List

AccName	ExpectedRevenue	Amount
A1	5000	10000
B2	500	250
A7	10	10

Sales List

AccName	BWS
A1	500
B5	-100
H9	25

Final List

AccName	ExpectedRevenue	Amount	Sales
A1	5000	10000	500
B2	500	250	250
B5	00	00	-100
A7	10	10	0
H9	0	0	25

CLASS

public class VTPv1 {       
    Public List<AggregateResult> OppGroup {get; set;}
    Public List<AggregateResult> SalesGroup {get; set;}
    
    public VTPv1() {          
        OppGroup    = [SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Name];
        SalesGroup  = [SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name];          
    }
}

VISUAL PAGE

<apex:page controller="VTPv1">        
    <apex:pageBlock title="Opportunities Group">
        <apex:pageBlockTable value="{!OppGroup}" var="oppg">  
            <apex:column value="{!oppg['AccName']}">
                <apex:facet name="header">Account Name</apex:facet>
            </apex:column>
            <apex:column value="{!oppg['Rev']}">
                <apex:facet name="header">Expected Revenue</apex:facet>
            </apex:column>
            <apex:column value="{!oppg['Fin']}">
                <apex:facet name="header">Amount</apex:facet>
            </apex:column>             
        </apex:pageBlockTable> 
    </apex:pageBlock>           
    <br/>           
    <apex:pageBlock title="Sales Group">
        <apex:pageBlockTable value="{!SalesGroup}" var="salesg">  
            <apex:column value="{!salesg['AccName']}">
                <apex:facet name="header">Account Name</apex:facet>          
            </apex:column>
            <apex:column value="{!salesg['BWS']}">
                <apex:facet name="header">Sales</apex:facet>          
            </apex:column>
        </apex:pageBlockTable> 
    </apex:pageBlock>                
</apex:page>

Update 22.10.21 I tried to create a new class with two maps, but I don't know how can I display the results at the visualforce page.

public class VTPv2 {
    public String CombinedSummary { get; set; }
    
    public VTPv2() {      
    }
    
    Public class CombinedSummary {
        public String AccName {get; set;}
        public Decimal expectedRevenue {get; set;}
        public Decimal amount {get; set;}
        public Decimal BWS {get; set;}  
        
        Public CombinedSummary (AggregateResult OppAr, AggregateResult SalAr) {
            AccName = (String) OppAr.get('AccName');
            expectedRevenue = (Decimal) OppAr.get('expectedRevenue');
            amount = (Decimal) SalAr.get('amount');
            BWS = (Decimal) SalAr.get('BWS');
        }
    }
    
    Public List<CombinedSummary> generateWrappers() {
        
        List<CombinedSummary> combinedSummaries = new List<CombinedSummary>();
        
        Map<Id, AggregateResult> OppGroup = new Map<Id, AggregateResult>();
        for(AggregateResult OppAr :[SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Id, Account.Name]) {
            OppGroup.put((Id)OppAr.get('Id'), OppAr);
        }
        
        Map<Id, AggregateResult> SalGroup = new Map <Id, AggregateResult>();
        for(AggregateResult SalAr :[SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Id, Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name]) {            
            SalGroup.put((Id)SalAr.get('Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Id'), SalAr);
        }
        
        Set<Id> AccSet = new Set<Id>();
        AccSet = SalGroup.keySet();
        AccSet.addAll(SalGroup.keySet());
        
        FOR (Id Id : AccSet) {
            combinedSummaries.add(new CombinedSummary(OppGroup.get(Id), SalGroup.get(Id)));
        }        
        Return combinedSummaries;        
    }  
}

Answer 1

At the very least, you would want to store the results of one of your queries in a map.

Given a public Map<String, Object> myMap {get; set;} in a controller or controller extension, you can access the value of a map in Visualforce using [].

<apex:repeat var="key" value="{!mapKeysList}">
    <apex:outputText value="{!myMap[key]}" />
</apex:repeat>

For readability though, I would suggest making a wrapper class and keep the logic in your controller. It can be a simple inner class that only contains a few variables. Your constructor would be responsible for populating a List of these wrapper class instances.

public class VTPv1 {
    public class ResultWrapper{
        // You may be able to store AggregateResult records, but it's simpler
        //   to access plain variables in Visualforce (and not much more typing)
        String name;
        Decimal expectedRevenue;
        Decimal amount;
        Decimal sales;
    }

    public List<ResultWrapper> resultList {get; set;}
    
    public VTPv1() {
        resultList = new List<ResultWrapper>();

        // We're still going to need to store one of these queries in a Map
        //   so that we can match records from the other query.
        // Shouldn't matter which one you store in a map.
        Map<String, AggregateResult> accNameToOppResult = new Map<String, AggregateResult>();
        for(AggregateResult ar :[SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Name]){
            accNameToOppResult.put((String)ar.get('AccName'), ar);
        }

        for(AggregateResult ar :[SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name]){
            // Here's where the bulk of the logic is
            // Find the matching record in the map, and then add a wrapper
            //   class instance to your list
            AggregateResult opportunityAR = accNameToOppResult.get((String)ar.get('AccName'));

            ResultWrapper combinedResult = new ResultWrapper();
            combinedResult.name = (String)ar.get('AccName');
            combinedResult.expectedRevenue = (Decimal)opportunityAR.get('rev');
            // ...and so on

            resultList.add(combinedResult);
        }
    }
}

You would then be able to iterate through that list in Visualforce using one of the normal methods (pageblocktable, datatable, repeat) and access individual fields using dot notation as per usual.

+edit:

Clarifying the following line
AggregateResult opportunityAR = accNameToOppResult.get((String)ar.get('AccName'));

It's not creating a new AggregateResult, it's retrieving an existing result from the first query.

The idea here is that to combine the results of your two queries, there needs to be a common piece of information shared between them. In my example, I used the AccName alias, though using an Id would likely be better.

The loop over the first query populates the map.

When you loop over the second query, since AccName is the shared piece of information, you can use that to get the corresponding value that was stored in the map from the first loop.

(String)ar.get('AccName') fetches the account name
accNameToOppResult.get(<actual account name here>) then fetches the corresponding Opportunity's AggregateResult.

You could break this into two lines as well, which might help clear things up.

String actualAccountName = (String)ar.get('AccName');
AggregateResult opportunityAR = accNameToOppResult.get(actualAccountName);

After that line of code executes, you're still in the loop for the second query.

ar is the result of the query on Umsatz__c, and opportunityAR is the matching result from the query on Opportunity (if there is any matching result).

After creating wrapper class instances (and adding them to a property, resultList in my example), displaying the result on a Visualforce page is then functionally the same as displaying a list of any other object.

<!-- pageblockTable or dataTable would also work -->
<apex:repeat var="wrapper" value="{!resultList}">
    <apex:outputText value="{!wrapper.name}" />
    <apex:outputText value="{!wrapper.expectedRevenue}" />
    <!-- and so on... -->
</apex:repeat>