0

This

SELECT Id, Name, (SELECT CountryISOCode__c, Name, Role__c FROM Person__r)
FROM Market__c

returns a row that looks like this:

Id NAME Person__r
a081.. C1 [{"A":"foo", "Name":"P2", "Role__c":"PR4"},{"A":"bar", "Name":"P7", "Role__c":"PR7"}]
a081.. C2 [{"A":"Jam", "Name":"P6", "Role__c":"PR6"},{"A":"Yum", "Name":"P8", "Role__c":"PR5"}]

The values in column Person__r do look like a json array. The field Role__c is a picklist which can be queried like this

SELECT Value, Label, IsActive 
FROM PicklistValueInfo 
WHERE EntityParticle.EntityDefinition.QualifiedApiName = 'Person__c' 
      AND EntityParticle.QualifiedApiName = 'Role__c' 

which returns

Value Label isActive
PR3 Ram false
PR4 Roo true
PR5 Rar true

I want to combine both queries and if possible create pivoted columns for every label for the picklist Role__c (Ram, Roo, Rar).

The final output should look like this

MarketName Ram Roo Rar
C1 2 0 1
C2 2 3 0
C7 0 1 1

For every market (C1, C2, C7) count how many persons (P2, P7, P6, P8) have which role (Ram, Roo, Rar)

Can this be done with SOQL?

1

1 Answer 1

1

You can get something close to this using an Aggregate Query.

Your query would be:

SELECT Market__r.Name, toLabel(Role__c), COUNT(Id) FROM Person__c GROUP BY Market__r.Name, Role__c

(I'm guessing what the lookup/object names are here because you'll need to base the query on the person object not the market object)

Your result would look like:

Market Name Role Count
C1 Ram 2
C1 Rar 1
C2 Ram 2
C2 Roo 3
C7 Roo 1
C7 Rar 1
3
  • The result works but it returns the columns Market.Name, Role.Value, Count(Id) example C1, PR4, 1. Is it possible from the picklist Role__c to display the Role__c.Label (Ram, Rar, Roo) instead of Role__c.Value (PR3, PR4, PR5)?
    – surfmuggle
    Feb 20 at 0:20
  • You can do toLabel(Role__c) to display the label instead of the value. (Just change it in the SELECT and not in the GROUP BY)
    – fred
    Feb 20 at 12:01
  • 1
    This does the trick. Thanks a lot.
    – surfmuggle
    Feb 21 at 13:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .