Hi someone ask me the same question: How to flatten a hierarchy self-referencing table, Google it and your thread and my first thought is Recursive CTE too, but then I'm just curios how someone do it before R.CTE. This is actually a much better way to do it and it's the fastest.

Try it on AdventureworksDW DimAccount table, the cool thing is: One line of code

SELECT

lev01.AccountKey id_01, lev01.AccountDescription name_01,

lev02.AccountKey id_02, lev02.AccountDescription name_02,

lev03.AccountKey id_03, lev03.AccountDescription name_03,

lev04.AccountKey id_04, lev04.AccountDescription name_04,

lev05.AccountKey id_05, lev05.AccountDescription name_05,

lev06.AccountKey id_06, lev06.AccountDescription name_06,

lev07.AccountKey id_07, lev07.AccountDescription name_07,

lev08.AccountKey id_08, lev08.AccountDescription name_08,

lev09.AccountKey id_09, lev09.AccountDescription name_09,

lev10.AccountKey id_10, lev10.AccountDescription name_10

FROM DimAccount lev01

LEFT OUTER JOIN DimAccount lev02 ON lev01.AccountKey = lev02.ParentAccountKey

LEFT OUTER JOIN DimAccount lev03 ON lev02.AccountKey = lev03.ParentAccountKey

LEFT OUTER JOIN DimAccount lev04 ON lev03.AccountKey = lev04.ParentAccountKey

LEFT OUTER JOIN DimAccount lev05 ON lev04.AccountKey = lev05.ParentAccountKey

LEFT OUTER JOIN DimAccount lev06 ON lev05.AccountKey = lev06.ParentAccountKey

LEFT OUTER JOIN DimAccount lev07 ON lev06.AccountKey = lev07.ParentAccountKey

LEFT OUTER JOIN DimAccount lev08 ON lev07.AccountKey = lev08.ParentAccountKey

LEFT OUTER JOIN DimAccount lev09 ON lev08.AccountKey = lev09.ParentAccountKey

LEFT OUTER JOIN DimAccount lev10 ON lev09.AccountKey = lev10.ParentAccountKey

WHERE lev01.ParentAccountKey IS NULL --AND lev01.AccountKey =1 uncomment to get just the balance sheet subtree

Found it at and courtesy of:

http://jpbi.blogspot.ca/2007/05/sql-trick-for-flattening-parent-child.html