CTE to Return all Items in Hierarchy

I have a table with a recursive hierarchy (i.e. ID, ParentID). For any item in this hierarchy, I want to be able to bring back a list of everything UP AND DOWN the hierarchy, along with the level for each row. Assume that a parent can only ever have a single child.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Sales_Executive_level](

[ExecutiveId] [bigint] IDENTITY(4000,1) NOT NULL,

[BranchId] [bigint] NOT NULL,

[ParentExecutiveID] [bigint] NULL,

[ExecutiveName] [nvarchar](350) NOT NULL,

[Password] [nvarchar](350) NOT NULL,

[RoleID] [int] NULL,

[IsActive] [bit] NULL,

CONSTRAINT [PK_Sales_Executive] PRIMARY KEY CLUSTERED

(

[ExecutiveId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[Sales_Executive_level] ON

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4000, 2000, NULL, N'Swati mishra', N'123456', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4001, 2000, 4000, N'Arjun Singh', N'47854889', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4002, 2000, 4000, N'Prakash jha', N'123', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4003, 2000, 4001, N'Pinku Saini', N'14578', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4004, 2000, 4001, N'Neeraj Tyaji', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4005, 2000, 4002, N'Jatin', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4006, 2000, 4002, N'Pankaj', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4007, 2000, 4003, N'Renu', N'113', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4008, 2000, 4003, N'Kushaan', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4009, 2000, 4003, N'bhupendra', N'12345', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4010, 2000, 4003, N'bhupendra', N'12345', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4011, 2000, 4004, N'rajesh ', N'234234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4012, 2000, 4004, N'hjfgjh', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4013, 2000, 4004, N'salesh', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4014, 2000, 4004, N'bhupendra', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4015, 2000, 4005, N'upendra', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4016, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4017, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4018, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4019, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4020, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4021, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4022, 2000, 4005, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4023, 2000, 4006, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4024, 2000, 4006, N'anju', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4025, 2000, 4006, N'bhuooo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4026, 2000, 4006, N'pilly', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4027, 2000, 4006, N'sally', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4028, 2000, 4006, N'erter', N'wert', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4029, 2000, 4006, N'riitu', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4030, 2000, 4006, N'rajuuu', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4031, 2000, 4007, N'turn', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4032, 2000, 4007, N'rahul', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4033, 2000, 4008, N'rajesh ', N'2234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4034, 2000, 4008, N'rea', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4035, 2000, 4009, N'yatin', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4036, 2000, 4009, N'reetu', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4037, 2000, 4010, N'pinku', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4038, 2000, 4010, N'jasmeet', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4039, 2000, 4011, N'manpreet', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4040, 2000, 4011, N'tmee', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4059, 2000, 4012, N'reee', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4060, 2000, 4012, N'mona', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4061, 2000, 4013, N'monika', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4062, 2000, 4013, N'UYOO', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4063, 2000, 4014, N'YUOOOU', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4064, 2000, 4014, N'RAHUL', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4065, 2000, 4015, N'PANKAJ', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4066, 2000, 4016, N'teeeo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4067, 2000, 4016, N'goolo', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4068, 2000, 4017, N'rttt', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4069, 2000, 4017, N'tofani', N'1234', 3, 1)

INSERT [dbo].[Sales_Executive_level] ([ExecutiveId], [BranchId], [ParentExecutiveID], [ExecutiveName], [Password], [RoleID], [IsActive]) VALUES (4070, 2000, 4018, N'allo', N'1234', 3, 1)

SET IDENTITY_INSERT [dbo].[Sales_Executive_level] OFF

;WITH Recursive_CTE AS (

SELECT

child.ExecutiveId,

CAST(child.ExecutiveName as varchar(100)) BusinessUnit,

CAST(NULL as bigint) ParentUnitID,

CAST(NULL as varchar(100)) ParentUnit,

CAST('' as varchar(100)) LVL,

CAST(child.ExecutiveId as varchar(100)) Hierarchy,

1 AS RecursionLevel

FROM [Sales_Executive_level] child

WHERE ExecutiveId = 4000 --your Id which you want to get all parent node

UNION ALL

SELECT

child.ExecutiveId,

CAST(LVL + child.ExecutiveName as varchar(100)) AS BusinessUnit,

child.ParentExecutiveID,

parent.BusinessUnit ParentUnit,

CAST('' + LVL as varchar(100)) AS LVL,

CAST(Hierarchy + ':' + CAST(child.ExecutiveId as varchar(100)) as varchar(100)) Hierarchy,

RecursionLevel + 1 AS RecursionLevel

FROM Recursive_CTE parent

INNER JOIN [Sales_Executive_level] child ON child.ParentExecutiveID = parent.ExecutiveId

)

SELECT * FROM Recursive_CTE ORDER BY Hierarchy

OPTION (MAXRECURSION 300);