Grandparent, parent and child hierarchy SQL server or Bread Crumb

1. Getting parent child relation using self join
2. Small introduction to CTE
(Recursive CTE)
3. Ordering records according to hierarchy
4. Keeping root parent with each record

Table script:

/****** Object:  Table [dbo].[HierarchyTB]    Script Date: 11/01/2015 13:08:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HierarchyTB](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpID] [int] NULL,
    [ParentID] [int] NULL,
    [Description] [varchar](50) NULL,
 CONSTRAINT [PK_HierarchyTB] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[HierarchyTB] ON
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (1, 1, 0, N'Main Parent Yogi')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (2, 2, 0, N'Main Parent Ron')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (3, 3, 0, N'Main Parent Don')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (4, 4, 2, N'Child Jaz')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (5, 5, 2, N'Child Rat')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (6, 6, 1, N'Child Tom')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (7, 7, 2, N'Child Jerry')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (8, 8, 3, N'Child Din')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (9, 9, 1, N'Child Minny')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (10, 10, 3, N'Child Micky')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (11, 11, 3, N'Child Goofy')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (12, 12, 3, N'Child Daisy')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (13, 13, 6, N'Sub Child Popey')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (14, 14, 6, N'Sub Child Zen')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (15, 15, 9, N'Sub Child Shin Chan')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (16, 16, 10, N'Sub Child Doremon')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (17, 17, 9, N'Sub Child Pikachu')
INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (18, 18, 8, N'Sub Child Tweety')
SET IDENTITY_INSERT [dbo].[HierarchyTB] OFF

Script for Query:

--Data Output
SELECT *
FROM HierarchyTB
--Using full qualified column names
SELECT HierarchyTB.EmpID,
       HierarchyTB.ParentID
FROM HierarchyTB
--Using Alias
SELECT H1.EmpID,
       H1.ParentID
FROM HierarchyTB AS H1
--Getting Parent and child name using SELF JOIN
SELECT H1.EmpID,
       H1.ParentID,
       isnull(h2.Description,'Root') [Parent Description],
       h1.Description [Self Description]
FROM HierarchyTB H1
LEFT JOIN HierarchyTB H2 ON H1.ParentID=h2.EmpID
--Introduction to CTE
WITH OutputCTE(EmployeeID,ParentID,ParentDescription,SelfDescription)
AS
(
SELECT H1.EmpID,
       H1.ParentID,
       isnull(h2.Description,'Root') [Parent Description],
       h1.Description [Self]
FROM HierarchyTB H1
LEFT JOIN HierarchyTB H2 ON H1.ParentID=h2.EmpID
)
SELECT * FROM OutputCTE;
--Getting output in hierarchical order
 WITH CTE AS
  (SELECT H1.EmpID,
          H1.ParentID,
          h1.Description [Parent],
          Description [Self Description],
          CAST(id AS varbinary(MAX)) [Level]
   FROM HierarchyTB H1
   WHERE h1.ParentID=0
   UNION ALL 
   SELECT H2.EmpID,
                    H2.ParentID,
                    c.[Self Description],
                    Description [Self Description],
                    c.[Level]+CAST(h2.id AS varbinary(MAX)) AS [Level]
   FROM HierarchyTB H2
   INNER JOIN CTE c ON h2.ParentID=c.EmpID)
SELECT *
FROM CTE
ORDER BY [Level]
 OPTION (MAXRECURSION 1000) 
;
--Getting hierarchical order with Root on each level
WITH CTE AS
  (SELECT H1.EmpID,
          H1.ParentID,
          h1.Description [Parent],
          Description [Self Description],
          CAST(id AS varbinary(MAX)) [Level],
          CAST (h1.id AS varchar(max)) [LevelID]
   FROM HierarchyTB H1
   WHERE h1.ParentID=0
   UNION ALL SELECT H2.EmpID,
                    H2.ParentID,
                    c.[Self Description],
                    Description [Self Description],
                    c.[Level]+CAST(h2.id AS varbinary(MAX)) AS [Level],
                                  c.[LevelID] + '>' + CAST (h2.id AS varchar(max)) [LevelID]
   FROM HierarchyTB H2
   INNER JOIN CTE c ON h2.ParentID=c.EmpID)
SELECT *
FROM CTE CROSS apply
  ( SELECT SUBSTRING(LevelID,1,CHARINDEX('>',LevelID+ '>')-1) ) c(RootLevelID)
ORDER BY [Level] OPTION (MAXRECURSION 1000)