Hi dharmendr,
I got one new but tricky solution. I am following this steps:
1) create temporary table with an extra field-temp.
2) insert values from Employee table in it. Insert actual Repto_ID in temp column.
3) Update ReportingManager = NULL where Temp = NULL
4) Select Query.
It works fine. Thanks a lot for your help :)
Following is the query:
CREATE TABLE [dbo].[EmployeesHierarchy](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](30) NULL,
[ReportingManager] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[EmployeesHierarchy] ON
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (1, N'Nancy Davolio', N'Sales Representative', 5)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (2, N'Andrew Fuller', N'CEO', 3)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (3, N'Janet Leverling', N'MD', NULL)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (4, N'Margaret Peacock', N'Sales Representative', 5)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (5, N'Steven Buchanan', N'Sales Manager', 2)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (6, N'Michael Suyama', N'Sales Representative', 8)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (7, N'Robert King', N'Sales Representative', 8)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (8, N'Laura Callahan', N'Sales Manager', 2)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (9, N'Anne Dodsworth', N'Sales Representative', 5)
SET IDENTITY_INSERT [dbo].[EmployeesHierarchy] OFF
DECLARE @EmployeesHierarchy AS TABLE(EmployeeId INT,Name VARCHAR(20),Designation VARCHAR(20),ReportingManager INT,Temp VARCHAR(50))
INSERT INTO @EmployeesHierarchy
SELECT E1.EmployeeId,E1.Name,E1.Designation,E1.ReportingManager,E2.ReportingManager
FROM EmployeesHierarchy E1
LEFT OUTER JOIN EmployeesHierarchy E2 ON E1.ReportingManager = E2.EmployeeId
WHERE ISNULL(E2.Name,'') > ''
UPDATE @EmployeesHierarchy
SET ReportingManager = NULL
WHERE ReportingManager NOT IN (SELECT EmployeeId FROM @EmployeesHierarchy)
SELECT * FROM @EmployeesHierarchy
