Dynamic Custom Fields and the RGFQ

Part of the problem with Custom Fields is that they're so custom! So they change. A lot. Meaning reporting on them isn't exactly easy without a lot of work. So here's a little snippet I cooked up with Alex's help to query the members of a tag with their values for any custom fields that happen to be on the Tag:

DECLARE @profile_id INT
SET @profile_id = 350


DECLARE @fields VARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX), @parameters NVARCHAR(MAX)


SELECT @fields=COALESCE(@FIELDS+',','')+'['+x.title+']'
FROM
(SELECT DISTINCT ccf.title
FROM
core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id=@profile_id
) AS x

SET @sql = 'SELECT *
FROM
(
SELECT cp.person_id, cp.last_name+'', ''+cp.first_name AS [person_name], ccf.title, REPLACE(ISNULL(cpmfv.selected_value,''''),''^'','' '') AS selected_value
FROM core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id = @profileID
) AS s
PIVOT
(
MAX(selected_value)
FOR [title] IN ('+@fields+')
) AS p'

SELECT @sql

SET @parameters = '@profileID INT'

EXEC sp_executesql @sql=@sql, @params=@parameters, @profileID=@profile_id

There's actually a lot of room for expansion from this point, including adding custom field groups, an active-only option, or adapting it to work for Groups/Missions/Appointments/etc. Maybe I'll come back and post some of those later.