SCCM Report how to find collection hierarchy

This Report will help you in identifying the collection that are member of any other parent collection.

SELECT
COL.CollectionID,
COL.Name,
COL.Comment,
CTSC.parentCollectionID
FROM
dbo.v_Collection COL,
dbo.v_CollectToSubCollect CTSC
WHERE
CTSC.subCollectionID = COL.CollectionID

The above report is to get collection with its parent collection but if you want to know the root map of particular, go with this post http://blogs.catapultsystems.com/mdowst/archive/2012/02/23/finding-sccm-sub-collections.aspx