SQL Query to find the collections Hierarchy 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.[Path],
fldr.[Name],
fldr.CollectionID FROM
folderHierarchy AS fldr