Google for Recursive CTE - basically a CTE that references itself.  You can set a level 0 in the CTE and then a derived level as the derived level of the parent +1

If you want to present the hierarchy as a single row you will need to use STUFF..FOR XML and PIVOT()

Have a go at some code and post back if you get stuck.