SOQL - Better query needed as well as field label in list

Question

I am trying to figure out how to write this as a better query.

Current thinking: (each field has a count of records for that State)

Select AL__c,AK__c, etc FROM Account WHERE...;

and then somehow transform that into a list where each field (label or api name) becomes a value.

Want I want in the end is a two dimensional set with all my state values in it: Example:

State, Account
AL, 200
AK, 15

This will be shown on a visualforce page as a table.

Edit : Idea! How about something like this? I am getting a "Initial term of field expression must be a concreaet sObject: list" error.

 selectedAccount = '001S000000qTmDxIAK';
    map <string,string>     mapStates   = new map<string,string>();
    list <Account> accStates = new list<Account>(
    [Select AL__c,AK__c FROM Account WHERE ID = :selectedAccount]);
    mapStates.add('AL', accStates.AL__c);
mapStates.add('AK', accStates.AK__c);

Answer 1

SOQL isn't the right tool for the job. In fact, if you use a **Field Set, you don't need to write any Apex at all!

<apex:page standardController="Account">
    <apex:pageBlock>
        <apex:pageBlockTable value="{!$ObjectType.Account.FieldSets.State_Fields}" var="field">
            <apex:column headerValue="State" value="{!$ObjectType.Account.fields[field].label}" />
            <apex:column headerValue="Account" value="{!Account[field]}" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

If you really want this structure in Apex, you can still use it in Apex:

Map<String, Decimal> states = new Map<String, Decimal>();
for (FieldSetMember member : SObjectType.Account.FieldSets.State_Fields)
    states.put(member.getLabel(), accountRecord.get(member.getFieldPath());

The simplest way to get these variables is through the StandardController, whether you're currently doing so or not:

public Account record { get; private set; }
public MyExtension(ApexPages.StandardController controller)
{
    List<String> fields = new List<String>();
    for (FieldSetMember member : SObjectType.Account.FieldSets.State_Fields)
        fields.add(member.getFieldPath();
    if (!Test.isRunningTest()) controller.addFields(fields);
    record = (Account)controller.getRecord();
}

Of course, you can query for it yourself if you wish. You would need to pull the Id in through page parameters most likely:

public static final String ID_PARAM = 'Id';
public Account record { get; private set; }
public MyController()
{
    Id recordId = ApexPages.currentPage().parameters.get(ID_PARAM);
    if (recordId == null) return;

    // same fields collection
    record = (Account)Database.query(
        'SELECT ' + String.join(fields, ',') + ' FROM Account WHERE Id = :recordId'
    );
}