Select [UserID],[Username],[DisplayName],[FirstName],[LastName],[Email],
[Address],[City],[Region],[PostalCode],[Country],[Telephone],[HowFind],[EffectiveDate]
from
(
SELECT u.UserID,
u.FirstName,
u.LastName,
u.Email,
u.Username,
u.DisplayName,
up.PropertyValue,
ppd.PropertyName,
ur.EffectiveDate
FROM Users u
cross join ProfilePropertyDefinition ppd
inner join UserProfile up on up.UserID = u.UserID and ppd.PropertyDefinitionID = up.PropertyDefinitionID
inner JOIN UserRoles ur ON u.UserID = ur.UserID
inner JOIN UserPortals ups ON u.UserID = ups.UserID
WHERE
u.UserID in (SELECT UserID from UserPortals Where PortalID = @PortalId) and
ppd.PortalID = @PortalId and
ur.RoleID = (SELECT [RoleID] FROM [Roles] WHERE RoleName='Restricted User' and PortalID = @PortalId)
) t
pivot
(
max(PropertyValue)
for PropertyName in ([Address],[City],[Region],[PostalCode],[Country],[Telephone],[HowFind])
) pvt
ORDER BY [UserID]
参考:http://msdn.microsoft.com/zh-cn/library/ms177410(v=SQL.100).aspx