I'm not sure if this is the proper way to do this but I am beginning to generate the results I want. With that said, how would I make my first column (in red) a linkable field that would direct the user to a user profile page when clicked? Any help is appreciated. Thank you.
SELECT
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),2,25) + ', ' +
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),2,25) AS Name,
MAX(CASE WHEN propertyName = 'Street' THEN propertyValue END) + '<br />' +
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) + ', ' +
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) + ' ' +
MAX(CASE WHEN propertyName = 'PostalCode' THEN propertyValue END) AS Address,
'<b>Home:</b> ' +
MAX(CASE WHEN propertyName = 'Telephone' THEN propertyValue END) + '<br />' +
'<b>Cell:</b> ' +
MAX(CASE WHEN propertyName = 'Cell' THEN propertyValue END) AS Phone
FROM
(SELECT DISTINCT PropertyName, PropertyValue, UserID FROM ProfilePropertyDefinition AS ppd
INNER JOIN UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d
INNER JOIN UserRoles ON d.UserID = UserRoles.UserID
INNER JOIN Users ON UserRoles.UserID = Users.UserID
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID
WHERE
(Roles.PortalID = 7 AND Roles.RoleName = 'Subscribers')
GROUP BY d.UserID, UserRoles.RoleID, Users.Email, Roles.RoleName
ORDER BY UserRoles.RoleID