SQL – Inner Join on Contacts and OptionSetMetadata tables

Post by: syed hussain in SQL

Summary

Using the Data Export Service, you can also query metadata to join customer data into a single flat table. For those trying to get OptionSetMetadata labels instead of code, here is a short SQL snippet to get you started.

select Id as contactguid, 
firstname,
lastname, 
address1_line1 as address, 
dbo.OptionSetMetadata.LocalizedLabel
from dbo.contact

INNER JOIN dbo.OptionSetMetadata ON dbo.contact.gendercode=dbo.OptionSetMetadata.[Option] AND dbo.OptionSetMetadata.OptionSetName = 'gendercode'

This is great when you want to create a single customer view normalized with all other associated customer data.

Tags: snippet sql
09 Oct 2019
0