SQL Query to find the collections Hierarchy : SCCM Colleections

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