WITH n(nodeId, alias,mastercontenttype,icon,thumbnail) AS
   (SELECT nodeId, alias,mastercontenttype,icon,thumbnail
    FROM [cmsContentType]
    UNION ALL
    SELECT nplus1.nodeId, nplus1.alias , nplus1.mastercontenttype, nplus1.icon,nplus1.thumbnail
    FROM [cmsContentType] AS nplus1, n
    WHERE n.nodeId = nplus1.masterContentType)

SELECT DISTINCT cmsContentType_1.alias,n.nodeId,n.alias,n.icon,n.thumbnail FROM n
LEFT OUTER JOIN
dbo.cmsContentType AS cmsContentType_1
ON n.masterContentType = cmsContentType_1.nodeId
ORDER BY n.nodeId