Did someone say lookups? I love lookups! Y'know, you can create your own. I've added dozens for all kinds of purposes. Mostly to reference from custom fields so the drop-downs can more easily be used multiple places, edited, or cross-referenced.
Here is a custom function I use all the time for reporting with lookups. You give it the lookup_id (luid) and the lookup_qualifier (or 0 if you just want the name of the lookup itself.
CREATE function [dbo].[cust_funct_luq_by_luid](@luid int, @qualifier int)
--enter a lookup_id and return the lookup_value or qualifier
DECLARE @luq varchar(2000)
SET @luq = (SELECT CASE WHEN @qualifier = 1 THEN lookup_qualifier
WHEN @qualifier = 2 THEN lookup_qualifier2
WHEN @qualifier = 3 THEN lookup_qualifier3
WHEN @qualifier = 4 THEN lookup_qualifier4
WHEN @qualifier = 5 THEN lookup_qualifier5
WHEN @qualifier = 6 THEN lookup_qualifier6
WHEN @qualifier = 7 THEN lookup_qualifier7
WHEN @qualifier = 8 THEN lookup_qualifier8
WHEN @qualifier = 0 THEN lookup_value END
WHERE lookup_id = @luid)
So, then you could use it for something like:
, dbo.cust_funct_luq_by_luid(marital_status_luid,0) AS marital_status
, dbo.cust_funct_luq_by_luid(member_status_luid,0) AS member_status
, dbo.cust_funct_luq_by_luid(member_status_luid,1) AS map_pin
Note: functions like this work great in the SELECT clause, but not recommended in the WHERE clause or it will make your query very slow.