Cacher is the code snippet organizer for pro developers

DECLARE @CollectionID AS varchar(8) DECLARE @Path AS varchar(2000)
SET @CollectionID = 'D010C0ED' ;

 WITH colCTE (ROW, collectionID, Name, parentCollID) AS
  ( SELECT 1 ,
           sub.subCollectionID ,
           col.Name ,
           sub.parentCollectionID
   FROM v_CollectToSubCollect sub
   INNER JOIN v_Collection col ON sub.subCollectionID = col.CollectionID
   WHERE col.CollectionID = @CollectionID
   UNION ALL SELECT cte.ROW + 1 ,
                              sub.subCollectionID ,
                              col.Name ,
                              sub.parentCollectionID
   FROM v_CollectToSubCollect sub
   INNER JOIN v_Collection col ON sub.subCollectionID = col.CollectionID
   INNER JOIN colCTE cte ON cte.parentCollID = sub.subCollectionID)
SELECT @Path = COALESCE(@Path + '\', '') + Name from colCTE WHERE row > 1 ORDER BY row DESC

SELECT
 @CollectionID AS 'CollectionID'
 ,col.Name,
 CASE
  WHEN @Path IS NULL THEN 'ROOT'
  ELSE @Path
 END AS 'Path'
FROM v_Collection col
WHERE CollectionID = @CollectionID