We've been doing this for years with T-SQL:
SELECT
@List = COALESCE(@List + '; ', '') + [LegalName] + COALESCE('; ' + [ShortName], '') + COALESCE('; ' + [AlternateName], '')
FROM dbo.prime_Org_Contact c INNER JOINdbo.prime_Organization o ON c.OrgID = o.ID
WHERE c.ContactID = @ContactIDRETURN @List
This returns a CSV list of orgs that a contact is associated with. We populate a search table with a bunch of these UDFS so we can full text search. Unfortunately, a view using a UDF that does data access cannot be full text indexed.
JR
