result will be in below format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

result will be in below format

Author

Topic

Mithun here
Starting Member

3 Posts

Posted - 2013-12-16 : 05:10:42

3. Using Employee Master Tabel( Employee Master)

Emp ID Empname Manager ID
1 CEO 0
2 ManagerA 1
3 ManagerB 1
4 ManagerC 1
5 Teamlead1 2
6 Teamlead2 2
7 Teamlead3 2
8 Teamlead4 3
9 Teamlead5 3
10 Teamlead6 3
11 Teamlead7 4
12 Teamlead8 4
13 Teamlead9 4
14 Employee1 5
15 Employee2 5
16 Employee3 5
17 Employee4 6
18 Employee5 6
19 Employee6 6
20 Employee7 7
21 Employee8 7
22 Employee9 8
23 Employee10 8
24 Employee11 8
25 Employee12 9
26 Employee13 9
27 Employee14 9
28 Employee15 10
29 Employee16 10
30 Employee17 11
31 Employee18 12

Write a query to get Data in following form
CEOID CEONAME Manager ID Manager Name TemaleadID TeamleadName EmployeeId EmployeeName
1 CEO 2 ManagerA 5 Teamlead1 14 Employee1
1 CEO 2 ManagerA 5 Teamlead1 15 Employee2
1 CEO 2 ManagerA 5 Teamlead1 16 Employee3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 05:41:54

[code]
declare @t table
(
[Emp ID] int,
Empname varchar(50),
[Manager ID] int
)
insert @t
values(1,'CEO',0),
(2,' ManagerA', 1),
(3,' ManagerB', 1),
(4,' ManagerC', 1),
(5,' Teamlead1', 2),
(6,' Teamlead2', 2),
(7,' Teamlead3', 2),
(8,' Teamlead4', 3),
(9,' Teamlead5', 3),
(10,' Teamlead6', 3),
(11,' Teamlead7', 4),
(12,' Teamlead8', 4),
(13,' Teamlead9', 4),
(14,' Employee1', 5),
(15,' Employee2', 5),
(16,' Employee3', 5),
(17,' Employee4', 6),
(18,' Employee5', 6),
(19,' Employee6', 6),
(20,' Employee7', 7),
(21,' Employee8', 7),
(22,' Employee9', 8),
(23,' Employee10', 8),
(24,' Employee11', 8),
(25,' Employee12', 9),
(26,' Employee13', 9),
(27,' Employee14', 9),
(28,' Employee15', 10),
(29,' Employee16', 10),
(30,' Employee17', 11),
(31,' Employee18', 12)

;With CTE
AS
(
SELECT t.[Emp ID] AS EmpID,t.Empname,t.[Manager ID],1 AS level,CAST(t.Empname AS varchar(max)) AS [Path]
FROM @t t
LEFT JOIN @t t1
ON t1.[Manager ID] = t.[Emp ID]
WHERE t1.[Emp ID] IS NULL
UNION ALL
SELECT t.[Emp ID],t.Empname,t.[Manager ID],c.level+1,CAST(c.Path + '/' + t.Empname AS varchar(max)) AS [Path]
FROM @t t
INNER JOIN CTE c
ON c.[Manager ID] = t.[Emp ID]
)
SELECT MAX(CASE WHEN Rn = 1 THEN EmpID END) AS CEOID,
MAX(CASE WHEN Rn = 1 THEN EmpName END) AS CEONAME,
MAX(CASE WHEN Level = 3 THEN EmpID END) AS ManagerID,
MAX(CASE WHEN Level = 3 THEN EmpName END) AS ManagerNAME,
MAX(CASE WHEN Level = 2 THEN EmpID END) AS TeamLeadID,
MAX(CASE WHEN Level = 2 THEN EmpName END) AS TeamLeadNAME,
MAX(CASE WHEN Level = 1 THEN EmpID END) AS EmpID,
MAX(CASE WHEN Level = 1 THEN EmpName END) AS EmpNAME
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(Path,CHARINDEX('/',Path + '/')-1) ORDER BY level DESC) AS Rn,*
FROM CTE
)t
GROUP BY LEFT(Path,CHARINDEX('/',Path + '/')-1)
OPTION (MAXRECURSION 0)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Mithun here
Starting Member

3 Posts

Posted - 2013-12-16 : 06:09:24

Hi,

i need only 3 row result as mentioned above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 06:12:41

Tell us the rules for selecting those three rows. there are lots of other employees so there should be a rule which will ensure only your required employees are returned.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Mithun here
Starting Member

3 Posts

Posted - 2013-12-16 : 06:19:06

initially the flow of the query will be like this..

select Manager_ID as teamleadid, emp_id as employee_id, empname as employe_name
from Employee_Master_table
where Emp_ID in (14,15,16)

like this relationship will continue upto CEO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 06:29:45

then use the same thing in given query


declare @t table
(
[Emp ID] int,
Empname varchar(50),
[Manager ID] int
)
insert @t
values(1,'CEO',0),
(2,' ManagerA', 1),
(3,' ManagerB', 1),
(4,' ManagerC', 1),
(5,' Teamlead1', 2),
(6,' Teamlead2', 2),
(7,' Teamlead3', 2),
(8,' Teamlead4', 3),
(9,' Teamlead5', 3),
(10,' Teamlead6', 3),
(11,' Teamlead7', 4),
(12,' Teamlead8', 4),
(13,' Teamlead9', 4),
(14,' Employee1', 5),
(15,' Employee2', 5),
(16,' Employee3', 5),
(17,' Employee4', 6),
(18,' Employee5', 6),
(19,' Employee6', 6),
(20,' Employee7', 7),
(21,' Employee8', 7),
(22,' Employee9', 8),
(23,' Employee10', 8),
(24,' Employee11', 8),
(25,' Employee12', 9),
(26,' Employee13', 9),
(27,' Employee14', 9),
(28,' Employee15', 10),
(29,' Employee16', 10),
(30,' Employee17', 11),
(31,' Employee18', 12)

;With CTE
AS
(
SELECT t.[Emp ID] AS EmpID,t.Empname,t.[Manager ID],1 AS level,CAST(t.Empname AS varchar(max)) AS [Path]
FROM  @t t
LEFT JOIN  @t t1
ON t1.[Manager ID] = t.[Emp ID]
WHERE t1.[Emp ID] IS NULL
AND t.[Emp ID] IN (14,15,16)
UNION ALL
SELECT t.[Emp ID],t.Empname,t.[Manager ID],c.level+1,CAST(c.Path + '/' + t.Empname AS varchar(max)) AS [Path]
FROM  @t t
INNER JOIN CTE c
ON c.[Manager ID] = t.[Emp ID]
)
SELECT MAX(CASE WHEN Rn = 1 THEN EmpID END) AS CEOID,
MAX(CASE WHEN Rn = 1 THEN EmpName END) AS CEONAME,
MAX(CASE WHEN Level = 3 THEN EmpID END) AS ManagerID,
MAX(CASE WHEN Level = 3 THEN EmpName END) AS ManagerNAME,
MAX(CASE WHEN Level = 2 THEN EmpID END) AS TeamLeadID,
MAX(CASE WHEN Level = 2 THEN EmpName END) AS TeamLeadNAME,
MAX(CASE WHEN Level = 1 THEN EmpID END) AS EmpID,
MAX(CASE WHEN Level = 1 THEN EmpName END) AS EmpNAME
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(Path,CHARINDEX('/',Path + '/')-1) ORDER BY level DESC) AS Rn,*
FROM CTE
)t
GROUP BY LEFT(Path,CHARINDEX('/',Path + '/')-1)
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources