I had a requirement a few years ago to do something similar where the flattened hierarchy where each level was a column, This is the thread http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx the end solution posted in the file BuildHierarchy.txt in the second from bottom post of the first page.

That's rather targeted for that one client, and had a few quirks based on their original data set, but the basis is in the 4th post.