Normally, you should be able to get the picklist display value and picklist item id in the filtered view of any entity. In some cases, you may need to work with StringMap table directly to get the picklist values for a specific field of a given entity. This could be the case if you are not using the filtered view for some reason or if you want to write a report based on the table or the non-filtered view, the following post is providng the query (queries) needed to do so.

To get the display value of any picklist, you need to use the StringMap table. If you are writing a custom ssrs (SQL Server Reporting Services) report for Dynamics CRM 4, you usually need to return the picklist display value not the picklist item id.

For example: in the opportunity entity, you have a picklist attribute “Rating” (attribute name opportunityratingcode). If you are writing a custom SQL report to add to CRM and you want to display the values of the attribute, you will find that the display value of the picklist is not stored in the filtered view of Opportunity (FilteredOpportunity). To get the display value of any picklist, you need to use the StringMap table.

There many ways to get the information from the StringMap table, I will mention two ways here:

1) using Inner Join (simple - one picklist only)
This way, you just simply join the two tables: your entity filteredview and the StringMap table
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
SELECT opp.name, opp.estimatedvalue_base, opp.closeprobability, StringMap.Value
FROM Opportunity as Opp INNER JOIN
StringMap ON Opp.opportunityratingcode = StringMap.AttributeValue
WHERE (StringMap.ObjectTypeCode = 3) AND (StringMap.AttributeName = N'opportunityratingcode')
AND (opp.accountid = 'input your accoung GUID here'

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2) using Temp table (my prefered way for multiple picklists)

This query is longer and uses/declares temp tables but it is the simplest way in case you have several picklists and you want to return the display value of multiple picklists. You just create several temp tables just like @TempSM, where each temp table returns the list of display values for each picklist and then join them together.

and so on. You create temp table for each picklist, join it with opportunity temp table and then carry on.

Note: This is one way of doing this. There are obviously many other shorter ways of doing this. I personally prefer temp tables a lot because although longer, they are simpler and easier to use. They also bypass any issues with null values in the right table of the join.

Not related Note: Always use filtered views when writing custom SQL reports on SSRS for CRM as they comply with CRM security.

Thanks for your input Paul. FilteredViews for any entity will also have the picklist value and id. I have just created this post for the rare cases where you need to use the StringMap table specifically.