Here is an SCCM report, that takes computer name and shows all collections that computer belongs to

Query used in this report:

select C.Name0, B.CollectionID,B.Name from v_ClientCollectionMembers A,

v_Collection B,

where A.CollectionID=B.CollectionID

and A.ResourceID = C.ResourceID

and C.Name0= @Name