Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
I am doing mini project on employee profile management.. I want to display the employee in hierarchical order.. i have the head of each employee in database.. how to display that Frown | :( Frown | :(
please help me...


The display should be like this...


CEO:name
PROJECT: name
PROJECT LEADER:name
PROJECT MEMBERS: 1.
2.
PROJECT2: name
PROJECT LEADER:name
PROJECT MEMBERS: 1.
2.
PROJECT3: name
PROJECT LEADER:name
PROJECT MEMBERS: 1.
2.



please help me to do this Frown | :( :(
Posted 1-Mar-13 22:07pm
Comments
willington.d at 2-Mar-13 3:16am
   
Please provide table structue of this data if possible...

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi Thenmozhi

Look into the below example and execute step by step. you could get an idea:

The blow also display details of employes in hierarchical:

CREATE TABLE #MyEmployees
(
	EmployeeID smallint NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID smallint NOT NULL,
	ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
INSERT INTO #MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
 
--ORIGINAL TABLE
SELECT * FROM #MyEmployees;
 
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM #MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM #MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
--HIERARCHY
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
 
DROP TABLE #MyEmployees

Regards
Willington
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 184
1 Sergey Alexandrovich Kryukov 154
2 Sascha Lefévre 120
3 Abhinav S 104
4 Jörgen Andersson 75
0 Sergey Alexandrovich Kryukov 6,628
1 OriginalGriff 6,298
2 Peter Leow 2,534
3 Maciej Los 2,437
4 Abhinav S 2,378


Advertise | Privacy | Mobile
Web01 | 2.8.150414.1 | Last Updated 2 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100