SQL important Hierarchy Queries

Common type expression for employee organisation hierarchy             

  ,OrgHierarchyLevel             
  As             
  (             
   SELECT Id, [Name], ParentID, LevelId FROM T_Organisation WHERE Id = @OrgId             
   UNION All             
   SELECT O.Id, O.[Name], O.ParentID, O.LevelId FROM T_Organisation O             
   INNER JOIN OrgHierarchyLevel OHL ON O.Id = OHL.ParentID             
  )           

Common type expression for employee organisation hierarchy sequence within one row using PIVOT Query:

DECLARE @OrgId INT = 100
;WITH OrgHierarchyLvl
As
(
SELECT
Id, Name, ParentID, LevelId
FROM
Organisation WHERE Id = @OrgId
UNION All
SELECT
ic.Id, ic.Name, ic.ParentID, ic.LevelId
FROM Organisation ic
INNER JOIN OrgHierarchyLvl cte ON ic.Id = cte.ParentID
)
,OrgSequence AS
(
SELECT @OrgId as OrgId,* FROM
(
SELECT LevelId,Name from OrgHierarchyLvl
) AS SourceTable
PIVOT(Max(Name) FOR [LevelId] IN ([1],[2],[3],[4],[5],[6],[7],[8])
) AS PivotTable
)
SELECT OrgId,[1] AS [Level 1 Org], [2] AS [Level 2 Org], [3] AS [Level 3 Org], [4] AS [Level 4 Org],
[5] AS [Level 5 Org], [6] AS [Level 6 Org], [7] AS [Level 7 Org], [8] AS [Level 8 Org]  FROM OrgSequence