;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
FROM         folderHierarchy AS fldr INNER JOIN v_UserInstancePermissionInfo ON fldr.CollectionID = v_UserInstancePermissionInfo.InstanceKey

 

ORDER BY fldr.Name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Category

Uncategorized

Tags

, , , , , ,