I am trying to list all my registered users, similar to the list one get from the user control panel.

This is the sql script I am using to list registered users on the report module of my DNN (4.5.3):

SELECT     MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END) AS Nombre,
           MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END) AS Apellido,
           
           MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) AS Ciudad,
     MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) AS Estado,
           MAX(CASE WHEN propertyName = 'Country' THEN propertyValue END) AS País

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     (d.UserID > 2)

GROUP BY d.UserID, UserRoles.RoleID, Users.Email, Roles.RoleName
ORDER BY UserRoles.RoleID

And here is the result I get:

The list mutiplies (by 3) all registered users. Can anyone help with the right sql script to eliminate de extra two duplicates.

Thank you a lot for your help.

Mr. Seem

Terrific, worked nicely!

Gracias Amigo

Mr. Seem