Hierarchy Query

This I Took from another Blog but is so cool I have to re post it,

This is to make a level hierarchy query

Only SQL server 2008 and up

with MyCTE AS
(
SELECT      
TYPEID,
ParentTypeId,
TYPE,
1 as level,
CAST(CAST(TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM          
TASKTYPE
WHERE      
(ParentTypeId  IS NULL)
UNION ALL

SELECT      
TY.TYPEID,
TY.ParentTypeId ,
TY.TYPE,
EL.level + 1,
CAST(el.SortPath + CAST(TY.TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM            TASKTYPE as TY
INNER JOIN MyCTE AS el on TY.ParentTypeId  = el.TypeID
WHERE        (TY.ParentTypeId  IS NOT NULL)

)
Select Typeid, parenttypeid, type = SPACE((level-1)*4)+type , level
from MyCTE
ORDER BY SortPath

Enjoy