All,

I am trying to get data from the dnn_userprofile table in a horizontal structure..that is, propertynames as columns.

The following is the query I am using, and I get the data I want..but the performance sux big time..I know my query is not good..but atleast I came this far.

I have around 90K users in mysite (yes..its still in development..but I wrote a script to generate so many users, as I wanted to check how much db size it will grow etc)

SELECT ROW_NUMBER() OVER (order by userid,username,firstname,DateOfBirth,gender,city) as rowid, userid, username,firstname,DateOfBirth,gender,city FROM
(SELECT dp.userid, username, propertyname,propertyvalue FROM dnn_userprofile dp
    INNER JOIN dnn_profilepropertydefinition as dpp on dp.propertydefinitionid = dpp.propertydefinitionid
    INNER JOIN dnn_users as du on dp.userid = du.userid WHERE IsSuperUser = 0
) src PIVOT (min(propertyvalue) FOR src.propertyname IN (firstname,DateOfBirth,gender,city) ) AS pvt WHERE (1 = 1)

Can anyone please let me know how can I improve the performance or even if you can suggest a different query then Its fine..I basically want the data returned in the following format

RowID   userid    username   firstname   DateOfBirth   Gender    City

1           123        myname    pete          10/10/10       M             New York

2           ai12        aname      mark         9/9/20          M              Las Vegas

THanks in advance