I see I can't get anyone to do my work for me. Here's the syntax I worked out, pls let me know if there's an easier way to do it. This will return a result set with a single row per user showing all requested field values and named custom properties. The pivot requires an aggregate, hence "Max". Seems to work.
Declare @Site int
Set @Site = 1
;With userData as (
select u.email,u.Gender,u.ProfileApproved,u.Country,u.State,u.Occupation,u.Interests,u.Signature,u.DateCreated
,p.PropertyName,p.PropertyValueString
from mp_users u
inner join mp_UserProperties p on u.UserGuid = p.UserGuid
Where SiteID = @Site
)
select * from userData o
PIVOT (MAX(PropertyValueString) for PropertyName in (Address1,Address2,County,[Home Tel],[Home Fax],[Home Email])) p