Umbraco Member information in one Row

Posted 20 Jul 17 by Darren Welch

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 ) piv

 

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





Post Comments

Darren Welch

By

Darren Welch


  20 Jul 17


Contact me online

 Facebook

 Twitter

 Linked In

 Git Hub

Related Content




Umbraco Backoffice default values

05 Feb 2017




Connect to SQl Server from command prompt

05 Feb 2017




How to prioritise a string in select statement

15 May 2017