Umbraco 7 SQL Member export with properties

U7-Member-properties.sql

WITH CurrentMemberVersion AS
(
    SELECT
        cv.ContentId,
        cv.VersionId,
        ROW_NUMBER() OVER (
            PARTITION BY cv.ContentId
            ORDER BY cv.VersionDate DESC, cv.Id DESC
        ) AS rn
    FROM cmsContentVersion cv
),
MemberPropertyValues AS
(
    SELECT
        m.nodeId AS MemberId,
        pt.alias AS PropertyAlias,
        CASE
            WHEN pd.dataNvarchar IS NOT NULL THEN CAST(pd.dataNvarchar AS nvarchar(max))
            WHEN pd.dataNtext    IS NOT NULL THEN CAST(pd.dataNtext    AS nvarchar(max))
            WHEN pd.dataInt      IS NOT NULL THEN CAST(pd.dataInt      AS nvarchar(max))
            WHEN pd.dataDecimal  IS NOT NULL THEN CAST(pd.dataDecimal  AS nvarchar(max))
            WHEN pd.dataDate     IS NOT NULL THEN CONVERT(nvarchar(30), pd.dataDate, 126)
            ELSE NULL
        END AS PropertyValue
    FROM cmsMember m
    INNER JOIN CurrentMemberVersion cmv
        ON cmv.ContentId = m.nodeId
       AND cmv.rn = 1
    INNER JOIN cmsPropertyData pd
        ON pd.contentNodeId = m.nodeId
       AND pd.versionId = cmv.VersionId
    INNER JOIN cmsPropertyType pt
        ON pt.id = pd.propertytypeid
)
SELECT
    m.nodeId AS MemberId,
    n.uniqueId AS MemberKey,
    n.[text] AS MemberName,
    m.LoginName AS Username,
    m.Email,
    ct.alias AS MemberTypeAlias,
    ctn.[text] AS MemberTypeName,
    n.createDate,
    n.path,
    n.level,
    n.sortOrder,
    Groups.GroupNames,

    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberApproved' THEN mpv.PropertyValue END) AS umbracoMemberApproved,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberLockedOut' THEN mpv.PropertyValue END) AS umbracoMemberLockedOut,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberFailedPasswordAttempts' THEN mpv.PropertyValue END) AS umbracoMemberFailedPasswordAttempts,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberLastLogin' THEN mpv.PropertyValue END) AS umbracoMemberLastLogin,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberLastLockoutDate' THEN mpv.PropertyValue END) AS umbracoMemberLastLockoutDate,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberLastPasswordChangeDate' THEN mpv.PropertyValue END) AS umbracoMemberLastPasswordChangeDate,
    MAX(CASE WHEN mpv.PropertyAlias = 'umbracoMemberComments' THEN mpv.PropertyValue END) AS umbracoMemberComments,

    MAX(CASE WHEN mpv.PropertyAlias = 'firstName' THEN mpv.PropertyValue END) AS firstName,
    MAX(CASE WHEN mpv.PropertyAlias = 'lastName' THEN mpv.PropertyValue END) AS lastName,
    MAX(CASE WHEN mpv.PropertyAlias = 'company' THEN mpv.PropertyValue END) AS company,
    MAX(CASE WHEN mpv.PropertyAlias = 'phone' THEN mpv.PropertyValue END) AS phone,
    MAX(CASE WHEN mpv.PropertyAlias = 'mobile' THEN mpv.PropertyValue END) AS mobile,
    MAX(CASE WHEN mpv.PropertyAlias = 'address' THEN mpv.PropertyValue END) AS address,
    MAX(CASE WHEN mpv.PropertyAlias = 'zip' THEN mpv.PropertyValue END) AS zip,
    MAX(CASE WHEN mpv.PropertyAlias = 'city' THEN mpv.PropertyValue END) AS city,
    MAX(CASE WHEN mpv.PropertyAlias = 'country' THEN mpv.PropertyValue END) AS country

FROM cmsMember m
INNER JOIN umbracoNode n
    ON n.id = m.nodeId
LEFT JOIN cmsContent c
    ON c.nodeId = m.nodeId
LEFT JOIN cmsContentType ct
    ON ct.nodeId = c.contentType
LEFT JOIN umbracoNode ctn
    ON ctn.id = ct.nodeId
LEFT JOIN MemberPropertyValues mpv
    ON mpv.MemberId = m.nodeId
OUTER APPLY
(
    SELECT STUFF(
    (
        SELECT ', ' + gn.[text]
        FROM cmsMember2MemberGroup mmg
        INNER JOIN umbracoNode gn
            ON gn.id = mmg.MemberGroup
        WHERE mmg.Member = m.nodeId
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, '')
    AS GroupNames
) Groups
WHERE n.nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560'
GROUP BY
    m.nodeId,
    n.uniqueId,
    n.[text],
    m.LoginName,
    m.Email,
    ct.alias,
    ctn.[text],
    n.createDate,
    n.path,
    n.level,
    n.sortOrder,
    Groups.GroupNames
ORDER BY n.[text];