Umbraco Member information in one Row

The membership model allows you to create custom fields for your members, this means that each piece of information will be stored in it's own row, allowing you to add as much information as you like.

So for our example, our members contain custom data of:

• Salutation
• First Name
• Last Name
• Telephone
• Address

This is stored in the CMSPropertyData and the CMSPropertyType tables.

So down to business....

You need to use SQLs Pivot method.

 

SELECT 
   salutation
   ,firstname
   ,lastname
   ,telephone
   ,[address]
FROM
(
  SELECT 
     m.Email
     ,m.LoginName
     ,CAST(COALESCE(pd.dataNVarchar,pd.datantext) AS VARCHAR(1000)) AS [value]
     ,pt.Name as [columnname]
  FROM cmsMember m
  JOIN cmsPropertyData pd
  ON   m.nodeId = pd.contentNodeId
  JOIN cmsPropertyType pt
  ON   pt.id = pd.propertytypeid
) d
PIVOT
(
  MAX(value)
  FOR columnname in (salutation,firstname,lastname,telephone,[address]
)) AS pivot_table

 

It's as simple as that. Just replace the columns with the name of your columns.

Comment

---