Cacher is the code snippet organizer for pro developers

WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path]) AS (
SELECT CollectionID,
       [Name],
       ParentCollectionID,
       CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
            FROM
              (SELECT CollectionID, [Name], ParentCollectionID
               FROM v_Collection
               INNER JOIN v_CollectToSubCollect ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
            WHERE ParentCollectionID = 'COLLROOT'
            UNION ALL
            SELECT child.CollectionID, child.Name, child.ParentCollectionID, parent.[Path]+child.[Name]+'/' AS [Path]
            FROM
              (SELECT CollectionID, [Name], ParentCollectionID
               FROM v_Collection
               INNER JOIN v_CollectToSubCollect ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
            INNER JOIN folderHierarchy AS parent ON parent.CollectionID = child.ParentCollectionID)
SELECT fldr.CollectionID,
       fldr.Name,
       fldr.Path,
       v_UserInstancePermissionInfo.UserName AS [Ersteller]
FROM folderHierarchy AS fldr
INNER JOIN v_UserInstancePermissionInfo ON fldr.CollectionID = v_UserInstancePermissionInfo.InstanceKey
ORDER BY fldr.Name