T-SQL for Picklist Values in CRM 2011

How to Write T-SQL for Picklist Values in Microsoft Dynamics CRM

Getting at the Picklist Values (a.k.a., Option Sets) is not easy.  It’s not hard either.  As you know, the picklist values are stored in the entity as numerical values, not the character-based descriptive values.  But how to get at the descriptive values that are useful to we humans?

Here’s a sample query giving you T-SQL for Picklist Values.  This example is for Leads (ObjectTypeCode 4), and a custom picklist value for County (sync_county).  The key table you want to get access to is the StringMap table.  But you have to put a couple of strategic filters in place to make it work.

1
2
3
4
5

SELECT LeadBase.CompanyName, LeadBase.FirstName, LeadBase.LastName, LeadExtensionBase.Sync_County, StringMap.AttributeName, StringMap.Value
FROM LeadBase INNER JOIN
LeadExtensionBase ON LeadBase.LeadId = LeadExtensionBase.LeadId INNER JOIN
StringMap ON LeadExtensionBase.Sync_County = StringMap.AttributeValue
WHERE (StringMap.ObjectTypeCode = 4) AND (StringMap.AttributeName = N'sync_county')